# Qualified

• Tables, relationships, keys, constraints understanding

• DDL, DML, DCL understanding

• SQL data types

• SQL operators, functions

• Data manipulation (insert, update, delete)

• Retrieving data (simple select statement)

• Joins understanding

# Tables, relationships, keys, constraints understanding

# Table

Basically a collection of related data entries and it consists of numerous columns and rows. Table is the most common and simplest form of data storage in a relational database

Customers:

• field

  column in a table that is designed to maintain specific information about every record in the table.

  ID, NAME, AGE, ADDRESS and SALARY.

• record

  is also called as a row of data is each individual entry that exists in a table.

  1	Khilan	25	Delhi	1500.00

• column

  vertical entity in a table that contains all information associated with a specific field in a table.

  NAME
  Ramesh
  Khilan

# Relationships

• One to One

  If each address can belong to only one customer, this relationship is "One to One"

  

• One to Many and Many to One

  Customers can make many orders (Each customer may have zero, one or multiple orders. But an order can belong to only one customer.);

  

• Many to Many

  each order can contain multiple items. And each item can also be in multiple orders.

  

• Self Referencing

  let's say you have a referral program. Customers can refer other customers to your shopping website.

  

# Keys

• Super Key Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are a subset of Super Keys.

• Primary Key Primary key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

• Unique Key A unique key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.

• Alternate key An Alternate key is a key that can be work as a primary key. Basically, it is a candidate key that currently is not a primary key.

• Candidate Key A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

• Composite/Compound Key Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

• Foreign Key Foreign Key is a field in a database table that is Primary key in another table. It can accept multiple null, duplicate values.

# Constraints

the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. :::

Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table.

• NOT NULL Constraint − ensures that a column cannot have a NULL value.

• DEFAULT Constraint − provides a default value for a column when none is specified.

• UNIQUE Constraint − ensures that all the values in a column are different.

• PRIMARY Key − uniquely identifies each row/record in a database table.

• FOREIGN Key − uniquely identifies a row/record in any another database table.

• CHECK Constraint − ensures that all values in a column satisfy certain conditions.

• INDEX − used to create and retrieve data from the database very quickly.

# DDL, DML, DCL understanding

• DDL (Data Definition Language)

  Command	Description
  CREATE	Creates a new table, a view of a table, or other object in the database
  ALTER	Modifies an existing database object, such as a table
  DROP	Deletes an entire table, a view of a table or other objects in the database

• DML (Data Manipulation Language)

  Command	Description
  SELECT	Retrieves certain records from one or more tables
  INSERT	Creates a record
  UPDATE	Modifies records
  DELETE	Deletes records
  MERGE	Merge records

• DCL (Data Control Language)

  Command	Description
  GRANT	Gives a privilege to user
  REVOKE	Takes back privileges granted from user

# SQL data types

SQL Data Type is an attribute that specifies the type of data of any object. Each column, variable and expression has a related data type in SQL. You can use these data types while creating your tables. You can choose a data type for a table column based on your requirement.

SQL Server offers six categories of data types:

# Exact Numeric Data Types

# Approximate Numeric Data Types

# Date and Time Data Types

Note − Here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.

# Character Strings Data Types

# Unicode Character Strings Data Types

# Binary Data Types

# Misc Data Types

# SQL operators, functions

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

Unary Operators: applied to only one operand at the time; the typical format is <operator><operand>

Binary Operators: applied to two operands at the time; they usually appear in format <operand><operator><operand>

• Arithmetic operators
• Comparison operators
• Logical operators
• Operators used to negate conditions

# SQL Arithmetic Operators

assume 'variable a' holds 10 and 'variable b' holds 20, then −

# SQL Comparison Operators

assume 'variable a' holds 10 and 'variable b' holds 20, then −

# SQL Logical Operators

here is a list of all the logical operators available in SQL.

# МАТЕМАТИЧЕСКИЕ ФУНКЦИИ

эти функции применяются для чисел.

# СИМВОЛЬНЫЕ ФУНКЦИИ

эти функции могут быть применены для строк текста, либо из столбцов текстовых типов данных, либо из строк литерных текстов, или же комбинация из этих двух.

# ФУНКЦИИ ДАТЫ И ВРЕМЕНИ

эти функции выполняются только для допустимых значений даты или времени.

# ДРУГИЕ ФУНКЦИИ

эта функция может быть применена к любому типу данных.

# Data manipulation (insert, update, delete)

# INSERT INTO

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

There are two basic syntaxes of the INSERT INTO statement which are shown below.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data.

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

The SQL INSERT INTO syntax will be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example The following statements would create six records in the CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS 
VALUES (2, 'Muffy', 24, 'Indore', 10000.00 );

# Populate one table using another table

We can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table.

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

# UPDATE

The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax The basic syntax of the UPDATE query with a WHERE clause is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

We can combine N number of conditions using the AND or the OR operators

Example:

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS
SET ADDRESS = 'Pune'
WHERE ID = 6;

Now, the CUSTOMERS table would have the following records

If you want to modify all the ADDRESS and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough as shown in the following code block.

UPDATE CUSTOMERS
SET ADDRESS = 'Pune', SALARY = 1000.00;

Now, CUSTOMERS table would have the following records:

# DELETE

The SQL DELETE Query is used to delete the existing records from a table.

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

The basic syntax of the DELETE query with the WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example:

The following code has a query, which will DELETE a customer, whose ID is 6.

DELETE FROM CUSTOMERS
WHERE ID = 3;

If you want to DELETE all the records from the CUSTOMERS table, you do not need to use the WHERE clause and the DELETE query would be as follows:

DELETE FROM CUSTOMERS;

Now, the CUSTOMERS table would not have any record.

# Retrieving data (simple select statement)

# SELECT

The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

# Syntax

SELECT column1, column2, columnN FROM table_name;

If you want to fetch all the fields available in the field, then you can use the following syntax.

SELECT * FROM table_name;

# Examples

SELECT ID, NAME, SALARY
FROM CUSTOMERS;

SELECT *
FROM CUSTOMERS;

# WHERE

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.

The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc.

# Syntax

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

You can specify a condition using the comparison or logical operators like >, <, =, LIKE, NOT, etc.

# Examples

SELECT sname, city
FROM Salespeople
WHERE city = "Kyiv";

SELECT *
FROM Customers
WHERE rating > 200;

# AND | OR operators

The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators.

# Syntax

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

# Examples

SELECT *
FROM Customers
WHERE city = “San Jose”
AND rating > 200;

SELECT *
FROM Customers
WHERE city = "San Jose" OR NOT rating > 200;

SELECT *
FROM Customers
WHERE NOT (city = 'San Jose'
OR rating > 200);

# ORDER BY

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

# Syntax

SELECT column-list
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

# Examples

SELECT *
FROM CUSTOMERS
ORDER BY NAME, SALARY;

SELECT *
FROM CUSTOMERS
ORDER BY NAME DESC;

# GROUP BY

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

# Syntax

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

# Examples

SELECT NAME, SUM(SALARY)
FROM CUSTOMERS
GROUP BY NAME;

# Joins understanding

# Common example

Table 1 − CUSTOMERS

Table 2 − ORDERS

# WHERE

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

# Example

SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

# Result

Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol.

# INNER JOIN

returns rows when there is a match in both tables

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

# Syntax

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

# Example

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

# Result

# LEFT JOIN

returns all rows from the left table, even if there are no matches in the right table

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

# Syntax

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

# Example

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

# Result

# RIGHT JOIN

returns all rows from the right table, even if there are no matches in the left table

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

# Syntax

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

# Example

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

# Result

# FULL JOIN

returns rows when there is a match in one of the tables

The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

# Syntax

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

# Example

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

# Result

If your Database does not support FULL JOIN (MySQL does not support FULL JOIN), then you can use UNION ALL

# SELF JOIN

is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement

The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.

# Syntax

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

# Example

SELECT  a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

# Result