PCA24C02J �ADVANCED DATABASE TECHNOLOGY�UNIT-2

INTRODUCTION TO SQL

Overview of the SQL Query Language

The SQL language has several parts:

• Data-definition language (DDL). The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

• Data-manipulation language (DML). The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.

Integrity. The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.

INTRODUCTION TO SQL

Overview of the SQL Query Language

View definition. The SQL DDL includes commands for defining views.

• Transaction control. SQL includes commands for specifying the beginning

and ending of transactions.

• Embedded SQL and dynamic SQL. Embedded and dynamic SQL define how

SQL statements can be embedded within general-purpose programming languages, such as C, C++, and Java.

• Authorization. The SQL DDL includes commands for specifying access rights

to relations and views..

Data Types in SQL

• char(n). Fixed length character string, with user-specified length n.
• varchar(n). Variable length character strings, with user-specified maximum length n.
• int. Integer (a finite subset of the integers that is machine-dependent).
• smallint. Small integer (a machine-dependent subset of the integer domain type).
• numeric(p,d). Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly, but not 444.5 or 0.32)
• real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.
• float(n). Floating point number, with user-specified precision of at least n digits.

​

​

​

Data Types in SQL

Data Types in SQL

SQL Syntax

• An SQL relation is defined using the create table command:

create table r

(A₁ D₁, A₂ D₂, ..., A_n D_n,� (integrity-constraint₁),� ...,� (integrity-constraint_k))

• r is the name of the relation
• each A_i is an attribute name in the schema of relation r
• D_i is the data type of values in the domain of attribute A_i
• Example:

create table instructor (� ID char(5),� name varchar(20),� dept_name varchar(20),� salary numeric(8,2))

​

SQL Syntax

• Some of The Most Important SQL Commands
• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index
• All the SQL statements require a semicolon (;) at the end of each statement.

SQL Syntax

SQL CREATE TABLE Statement

In an SQL driven database, the data is stored in a structured manner, i.e. in the form of tables. To create a table, following syntax is used 

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY( one or more columns )

);

​

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

SQL Syntax

SQL DESC Statement

Every table in a database has a structure of its own.

To display the structure of database tables, we use the DESC statements. Following is the syntax −

​

​

DESC table_name;

​

DESC CUSTOMERS;

==========

SQL INSERT INTO Statement

The SQL INSERT INTO Statement is used to insert data into database tables. Following is the syntax −

INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);

​

INSERT INTO CUSTOMERS VALUES

(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),

(2, 'Khilan', 25, 'Delhi', 1500),

(3, 'kaushik', 23, 'Kota', 2000),

(4, 'Chaitali', 25, 'Mumbai', 6500));�

SQL Syntax

SQL SELECT Statement

In order to retrieve the result-sets of the stored data from a database table, we use the SELECT statement. Following is the syntax −

SELECT column1, column2....columnN FROM table_name;

​

To retrieve the data from CUSTOMERS table, we use the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

​

SQL UPDATE Statement

When the stored data in a database table is outdated and needs to be updated without having to delete the table, we use the UPDATE statement. Following is the syntax −

​

​

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

​

To see an 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;

​

SQL Syntax

SQL DELETE Statement

Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions. This is done using the DELETE FROM statement. Following is the syntax −

​

DELETE FROM table_name WHERE {CONDITION};

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

​

DELETE FROM CUSTOMERS WHERE ID = 6;

​

SQL DROP TABLE Statement

To delete a table entirely from a database when it is no longer needed, following syntax is used −

​

DROP TABLE table_name;

This query will drop the CUSTOMERS table from the database.

​

DROP TABLE CUSTOMERS;

​

SQL Syntax

SQL DELETE Statement

Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions. This is done using the DELETE FROM statement. Following is the syntax −

​

DELETE FROM table_name WHERE {CONDITION};

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

​

DELETE FROM CUSTOMERS WHERE ID = 6;

​

SQL DROP TABLE Statement

To delete a table entirely from a database when it is no longer needed, following syntax is used −

​

DROP TABLE table_name;

This query will drop the CUSTOMERS table from the database.

​

DROP TABLE CUSTOMERS;

​

SQL Syntax

SQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is implemented in SQL to delete the data of the table but not the table itself. When this SQL statement is used, the table stays in the database like an empty table. Following is the syntax −

​

TRUNCATE TABLE table_name;

​

Following query delete all the records of the CUSTOMERS table −

​

TRUNCATE TABLE CUSTOMERS;

​

SQL ALTER TABLE Statement

​

The ALTER TABLE statement is used to alter the structure of a table. For instance, you can add, drop, and modify the data of a column using this statement. Following is the syntax −

​

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_type};

​

Following is the example to ADD a New Column to the CUSTOMERS table using ALTER TABLE command −

ALTER TABLE CUSTOMERS ADD SEX char(1);

SQL Syntax

SQL WHERE Clause

The WHERE clause is used to filter rows from a table by applying a condition. Following is the syntax to retrieve filtered rows from a table −

​

SELECT column1, column2....columnN FROM table_name WHERE CONDITION;

​

The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000, using the SELECT statement −

​

SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000;

SQL Operators

Operators in SQL

Operators in SQL are symbols that help us to perform specific mathematical and logical computations on operands. An operator can either be unary or binary.

​

The unary operator operates on one operand, and the binary operator operates on two operands.

​

Types of Operators in SQL

Different types of operators in SQL are:

​

1. Arithmetic operator
2. Comparison operator
3. Logical operator
4. Bitwise Operators
5. Compound Operators

SQL Operators

Operators in SQL

SQL Arithmetic Operators

​

SQL Operators

Operators in SQL

SQL Bitwise Operators

​

SQL Comparison Operators

Comparison Operators in SQL are used to compare one expression’s value to other expressions. SQL supports different types of comparison operator, which are described below:

SQL Operators

SQL Logical Operators

Logical Operators in SQL are used to combine or manipulate conditions in SQL queries to retrieve or manipulate data based on specified criteria..

SQL Operators

SQL Bitwise Operators

Bitwise operators in SQL are used to perform bitwise operations on binary values in SQL queries, manipulating individual bits to perform logical operations at the bit level. Some SQL Bitwise Operators are:

SQL Operators

SQL Compound Operators

SQL - Expressions

• What is SQL Expression?
• An SQL expression is a combination of one or more values, operators and SQL functions that are all evaluated to a value. These SQL EXPRESSION(s) are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.
• Expressions are used in WHERE clause of an SQL query. As you might have already known, a WHERE clause specifies a condition that needs to be satisfied for the purpose of filtering records from a database table. This condition is comprised of either single or multiple expressions. These expressions are further classified into three types −
• Boolean Expressions
• Numeric Expressions
• Date and time Expressions

​

SQL - Expressions

• SQL Boolean Expressions
• SQL Boolean Expressions are SQL expressions that return only Boolean Datatype as a result. These expressions can be of two types −

​

• Boolean Expressions that check for equality of two values using SQL comparison operators. Here, equality of these values is a condition.
• Boolean Expressions can also contain one value paired with an SQL logical operator. In this case, the logic specified acts like a condition.

SQL - Expressions

• SQL Boolean Expressions

Example

Consider the CUSTOMERS table having the following records −

The following query is a simple example showing the usage of an SQL Boolean Expression −

​

SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

​

SQL - Expressions

• SQL Numeric Expressions
• SQL Numeric expressions are comprised of two operands and an SQL Arithmetic Operator. These expressions are used to perform any mathematical operation in any query. Hence, the operands must always be numerals and the return value will always be a number as well.
• Example- Following is a simple example showing the usage of SQL Numeric Expressions −
• SELECT 15 + 6;
• Output
• The output table is retrieved as − 21

SELECT COUNT(*) FROM CUSTOMERS;

The output is displayed as follows − 7

​

​

SQL - Expressions

• SQL Date Expressions
• SQL Date Expressions are used to compare date related values with current system date and time values. For instance, in a manufacturing company, items manufactured per year can be segregated by using date expressions in a WHERE clause.
• Counting from the first day of an year to the last day, the count of each item will be retrieved; once the required information is gathered, the company can use this information for their own purposes.

SELECT CURRENT_TIMESTAMP;

The output table is displayed as −

Current_Timestamp

2009-11-12 06:40:23

​

​

DDL (Data Definition Language)

• DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

​

• DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application.

​

DDL (Data Definition Language)

DDL Commands

DDL (DATA DEFINITION LANGUAGE):

• It is the first sub-language of SQL.
• It is used to define the database objects such as table, view, synonym, index and sequence.
• This language contains Six commands those are:

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE TABLE
5. RENAME
6. COMMENT

• Create objects in the database
• Change the structure of database object
• Deletes the database objects from the database
• Removes all the records from the database table
• Rename the table.
• Add comments to the Data Dictionary

Create Table Command

This command is used to create a table.

Syntax:

CREATE TABLE <TABLE_NAME> (

COL_NAME1 DATATYPE(SIZE),

COL_NAME2 DATATYPE(SIZE),

COL_NAME3 DATATYPE(SIZE), … ,

COL_NAMEn Datatype (size)

);

Create table student (

SNO number (3),

SNAME varchar2 (20),

MARKS number (3)

);

ALTER Command

• By using ALTER command we can perform the following task.
1. ADDING a new column or Constraints
2. DROPING an existing column or Constraint
3. MODIFYING column definitions
4. RENAMING a column

ADDING A NEW COLUMN

Syntax:

ALTER TABLE <TABLE_NAME> ADD (COL1_NAME DATA TYPE(SIZE),

COL2_NAME DATA TYPE(SIZE),

…

COLn_NAME DATA TYPE(SIZE));

​

• Add City column to the Student Table

ALTER table student ADD(city varchar2(10));

• Add State and Pincode columns to Student Table

ALTER table student ADD(state varchar2(10), pincode number(6));

​

Note: New column can be added only at last. The new column will have null values.

DROPING AN EXISTING COLUMN

Syntax:

ALTER TABLE <TABLE_NAME> DROP(COL1_NAME,COL2_NAME);

​

• Delete the column state from the student table

ALTER table student drop(state);

• Delete the columns say City and Pincode from the student table

ALTER table student drop(city, pincode);

MODIFYING A COLUMN

• MODIFYING A COLUMN: (increasing/decreasing the size of columns)

Syntax:

ALTER TABLE <TABLE_NAME> MODIFY(COL1_NAME DATA TYPE(SIZE));

Ex: ALTER table student modify (Sname varchar2(10)); ALTER table student modify (Sname varchar2(8));

Note:

• we can increase and decrease as well as the size of the column.
• We can decrease the column size only when existing column values can fit into new size
• By using modify keyword we can change the data type of a column.
• Column should be empty to change it’s data type.

RENAMING A COLUMN

Syntax:

ALTER TABLE <TABLE_NAME>

RENAME COLUMN<OLD_COL_NAME>TO <NEW_COL_NAME>;

​

​

Ex: Rename the column SAL to WAGES in EMP table

ALTER TABLE emp RENAME COLUMN SAL TO WAGES;

DROP Command

This command is used to remove the table from the database.

Syntax:

DROP TABLE <TABLE_NAME>;

Ex:

DROP table student; DROP table emp;

• It removes the table completely from the database along with Data and its

Structure

• Once the table is dropped, we cannot get its data back, as it gets removed

permanently

TRUNCATE Command

It is used to remove all the rows from the table.

Syntax: TRUNCATE TABLE <TABLE_NAME>;

Ex: TRUNCATE table student;

Select * from TAB; // ALL TABLE NAME’S ARE DISPLAYED

DELETE

• We can Roll Back the data.
• Rows are deleting temporally.
• Where clause can be used.
• Delete is sub language DML.

TRUNCATE

• We cannot Roll Back the data.
• Rows are deleting permanently.
• Where clause cannot be used.
• Delete is sub language DDL.

NOTE: When we use a Truncate command the table gets dropped and re-created. As the structure is

effected is called as a DDL command.

• All DDL command are permanent.
• All DML command are Temporary.

Rename

This command is used to change the table name.

Syntax: RENAME <OLD_TABLE_NAME> TO <NEW_TABLE_NAME>;

Ex: Rename student To student1; Rename SALGRADE To GRADE;

DML Commands in SQL

• DML is an abbreviation of Data Manipulation Language.
• The DML commands in Structured Query Language change the data present in the SQL database. We can easily access, store, modify, update and delete the existing records from the database using DML commands.
• Following are the four main DML commands in SQL:

​

1. SELECT Command
2. INSERT Command
3. UPDATE Command
4. DELETE Command

DML Commands in SQL

• SELECT DML Command
• SELECT is the most important data manipulation command in Structured Query Language. The SELECT command shows the records of the specified table. It also shows the particular record of a particular column by using the WHERE clause.
• Syntax of SELECT DML command
• SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_of_table;  
• SELECT * FROM Student;  
• SELECT * FROM Student WHERE Stu_Marks = 80;  

​

​

DML Commands in SQL

• INSERT DML Command
• INSERT is another most important data manipulation command in Structured Query Language, which allows users to insert data in database tables.

​

• Syntax of INSERT Command
• INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 , .... column_NameN )  VALUES (value_1, value_2, value_3, .... value_N ) ;   
• INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age) VALUES (104, Anmol, 89, 19);  
•  

​

DML Commands in SQL

• UPDATE DML Command
• UPDATE is another most important data manipulation command in Structured Query Language, which allows users to update or modify the existing data in database tables.
• Syntax of UPDATE Command
• UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N] WHERE CONDITION;   
•  

​

1. UPDATE Product SET Product_Price = 80 WHERE Product_Id = 'P102' ;  

DML Commands in SQL

• DELETE DML Command
• DELETE is a DML command which allows SQL users to remove single or multiple existing records from the database tables.
• This command of Data Manipulation Language does not delete the stored data permanently from the database. We use the WHERE clause with the DELETE command to select specific rows from the table.
• Syntax of DELETE Command
• DELETE FROM Table_Name WHERE condition;  

​

DELETE FROM Product WHERE Product_Id = 'P202' ;  

TCL Commands in SQL

• TCL stands for Transaction control language.
• A single unit of work in a database is formed after the consecutive execution of commands is known as a transaction.
• There are certain commands present in SQL known as TCL commands that help the user manage the transactions that take place in a database.
• COMMIT. ROLLBACK and SAVEPOINT are the most commonly used TCL commands in SQL.
• 1. COMMIT :
• This command is used to save the data permanently.
• Whenever we perform any of the DML command like -INSERT, DELETE or UPDATE, these can be rollback if the data is not stored permanently. So in order to be at the safer side COMMIT command is used.

Syntax:

commit;

​

TCL Commands in SQL

• 2. ROLLBACK :
• This command is used to get the data or restore the data to the last savepoint or last committed state. If due to some reasons the data inserted, deleted or updated is not correct, you can rollback the data to a particular savepoint or if savepoint is not done, then to the last committed state.

​

• Syntax:
• rollback;
• 3. SAVEPOINT :
• This command is used to save the data at a particular point temporarily, so that whenever needed can be rollback to that particular point.
• Syntax:
• Savepoint A;

DCL (Data Control Language) Commands in SQL

• DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
• List of DCL commands:
• Two important DCL commands and their syntax are:

DCL (Data Control Language) Commands in SQL

• DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
• List of DCL commands:
• Two important DCL commands and their syntax are:
• GRANT Command
• GRANT, as the name itself suggests, provides. This command allows the administrator to provide particular privileges or permissions over a database object, such as a table, view, or procedure. It can provide user access to perform certain database or component operations.
• In simple language, the GRANT command allows the user to implement other SQL commands on the database or its objects. The primary function of the GRANT command in SQL is to provide administrators the ability to ensure the security and integrity of the data is maintained in the database.
• GRANT SELECT ON student TO Aman; 
• This command will allow Aman to implement the SELECT queries on the student table. This will enable the user to read or retrieve information from the student table.  

​

DCL (Data Control Language) Commands in SQL

• DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
• List of DCL commands: Two important DCL commands and their syntax are:
• REVOKE Command
• As the name suggests, revoke is to take away. The REVOKE command enables the database administrator to remove the previously provided privileges or permissions from a user over a database or database object, such as a table, view, or procedure. The REVOKE commands prevent the user from accessing or performing a specific operation on an element in the database.
• In simple language, the REVOKE command terminates the ability of the user to perform the mentioned SQL command in the REVOKE query on the database or its component. The primary reason for implementing the REVOKE query in the database is to ensure the data's security and integrity.
• REVOKE SELECT ON student FROM Aman;  
• This will stop the user Aman from implementing the SELECT query on the student table. The user may be able to implement other queries in the database.

​

• .

Aggregate Functions

• Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a single value. SQL offers five built-in aggregate functions:

Average: avg

Minimum: min

Maximum: max

Total: sum

Count: count

• Theinputtosumandavgmustbeacollectionofnumbers,buttheotheroperators
• can operate on collections of nonnumeric data types, such as strings, as well

Aggregate Functions

• An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
• Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.
• The most commonly used SQL aggregate functions are:
• MIN() - returns the smallest value within the selected column
• MAX() - returns the largest value within the selected column
• COUNT() - returns the number of rows in a set
• SUM() - returns the total sum of a numerical column
• AVG() - returns the average value of a numerical column
• Aggregate functions ignore null values (except for COUNT()).

Aggregate Functions

• Demo SQL Database
• In this tutorial on aggregate functions, we will use the following table for examples:

​

​

​

​

​

​

​

​

�

Aggregate Functions

• Demo SQL Database
• In this tutorial on aggregate functions, we will use the following table for examples:
• Queries
• --Count the number of employees�SELECT COUNT(*) AS Total Employees FROM Employee;�Total Employees 6�-- Calculate the total salary�SELECT SUM(Salary) AS Total Salary FROM Employee;�Total Salary 3120�-- Find the average salary�SELECT AVG(Salary) AS Average Salary FROM Employee;�Average Salary 624�-- Get the highest salary�SELECT MAX(Salary) AS Highest Salary FROM Employee;�Highest Salary 802�-- Determine the lowest salary�SELECT MIN(Salary) AS Lowest Salary FROM Employee;
• Lowest Salary 403

​

​

​

​

​

​

​

�

Set Operations

• The SQL operations union, intersect, and except operate on relations and correspond to the mathematical set-theory operations ∪, ∩,and−.
• There are three primary set operators in SQL:

​

• UNION
• INTERSECT
• EXCEPT (or MINUS)

​

​

​

​

​

​

�

SET OPERATIONS

Types of Set Operation

1. Union
2. UnionAll
3. Intersect
4. Minus (Set difference)

​

​

​

​

​

​

​

​

Union Operation

For R ∪ S, The union of two relations, R and S, defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.

SET OPERATIONS

Union

• The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
• In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied.
• The union operation eliminates the duplicate rows from its resultset.

​

Syntax

​

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2;

​

​

​

​

​

SET OPERATIONS- UNIONS

Syntax

​

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2;

Example: The First table

​

​

​

​

​

The Second table

Union SQL query will be:

SELECT * FROM First   

UNION  

SELECT * FROM Second;  

SET OPERATIONS- UNION ALL

SQL UNION ALL

SQL UNION ALL is a powerful tool used to combine the results of two or more SELECT statements into a single result set. Unlike the UNION operator, which eliminates duplicate records, UNION ALL includes all duplicates. This makes UNION ALL it faster and more efficient when you don’t need to remove duplicates.

​

SELECT columns FROM table1�UNION ALL�SELECT columns FROM table2; 

Example: STUDENTS table:

​

​

​

​

​

​

TRIP_DETAIL Table:

SELECT * FROM STUDENTS�UNION ALL�SELECT * FROM TRIP_DETAIL;

SET OPERATIONS- SQL | MINUS Operator

SQL MINUS

The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query. In simple words, we can say that MINUS operator will return only those rows which are unique in only first SELECT query and not those rows which are common to both first and second SELECT queries..

​

​

​

SELECT NAME, AGE , GRADE

FROM Table1

MINUS

SELECT NAME, AGE, GRADE

FROM Table2

​

​

​

SET OPERATIONS- INTERSECTION Operation:

SQL INTERSECTION

A ∩ S where, A and S are the relations, symbol ‘∩’ is used to denote the Intersection operator.

​

The result of Intersection operation, which is denoted by A ∩ S, is a relation that basically includes all the tuples that are present in both A an S.

​

Example: Consider a relation Student(FIRST, LAST) and Faculty(FIRSTN, LASTN) given below :

​

​

Student INTERSECTION Faculty :

Student ∩ Faculty

SET OPERATIONS- Cartesian Product

For R × S, the Cartesian product operation defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.

​

Example:

Consider two relations

STUDENT(SNO, FNAME, LNAME) and

DETAIL(ROLLNO, AGE)

​

​

​

�

​

​

The Second table DETAIL

The First table STUDENT

On applying CROSS PRODUCT on STUDENT and DETAIL:

STUDENT ✕ DETAILS

SET OPERATIONS- Set difference

Set difference

For R − S The Set difference operation defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible.

​

It combines the result of two SELECT statements. Minus operator is used to display the rows which are present in the first query but absent in the second query.

It has no duplicates and data arranged in ascending order by default.

​

Example:

∏ writer (Nobels) − ∏ writer (papers)

​

Syntax:

SELECT column_name FROM table1  

MINUS  

SELECT column_name FROM table2;  

​

SELECT * FROM First   

MINUS  

SELECT * FROM Second;   The result set table will look like:

​

​

​

�

​

​

The Second table

The First table

SQL Functions

SQL Functions are built-in programs that are used to perform different operations on the database.

​

There are two types of functions in SQL:

​

Aggregate Functions

Scalar Functions

SQL Functions

SQL Aggregate Functions

SQL Aggregate Functions operate on a data group and return a singular output. They are mostly used with the GROUP BY clause to summarize data.

SQL Functions

SQL Scalar functions

SQL Scalar Functions are built-in functions that operate on a single value and return a single value.

Scalar functions in SQL helps in efficient data manipulation and simplification of complex calculations in SQL queries.

SQL Functions

https://www.geeksforgeeks.org/sql-functions-aggregate-scalar-functions/

SQL CLAUSES

The sql clauses can help filter out the data according to the users' needs.

The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT, UPDATE, DELETE, and JOIN.

Each clause has a syntax and its own set of rules and options. They can also be used in combination to create complex queries.

​

1. WHERE CLAUSE

A WHERE clause in SQL is used with the SELECT query, which is one of the data manipulation language commands. WHERE clauses can be used to limit the number of rows to be displayed in the result set, it generally helps in filtering the records. It returns only those queries which fulfill the specific conditions of the WHERE clause.

WHERE clause is used in SELECT, UPDATE, DELETE statement, etc.

Syntax of where clause with a select query to retrieve all the column values for every record from a table:

SELECT * FROM TABLENAME WHERE CONDITION;  

SELECT * FROM employees WHERE Salary > 50000;  

​

SQL CLAUSES

2. GROUP BY CLAUSE

The Group By clause is used to arrange similar kinds of records into the groups in the Structured Query Language.

The Group by clause in the Structured Query Language is used with Select Statement. Group by clause is placed after the where clause in the SQL statement.

The Group By clause is specially used with the aggregate function, i.e., max (), min (), avg (), sum (), count () to group the result based on one or more than one column.

The syntax of Group By clause:

SELECT * FROM TABLENAME GROUP BY COLUMNNAME;   

The above syntax will select all the data or records from the table, but it will arrange all those data or records in the groups based on the column name given in the query.

The syntax of Group By clause with Aggregate Functions:

SELECT COLUMNNAME1, Aggregate_FUNCTION (COLUMNNAME) FROM TABLENAME GROUP BY COLUMNNAME;  

​

SQL CLAUSES

​

SQL CLAUSES

​

Example 1:

Write a query to display all the records of the employees table but group the results based on the age column.

Query:

mysql> 

SELECT * FROM employees GROUP BY Age;  

SQL CLAUSES

3. HAVING CLAUSE:

When we need to place any conditions on the table's column, we use the WHERE clause in SQL. But if we want to use any condition on a column in Group By clause at that time, we will use the HAVING clause with the Group By clause for column conditions.

​

Syntax:

TABLENAME GROUP BY COLUMN NAME HAVING CONDITION;

SQL CLAUSES

Example 1:

Write a query to display the name of employees, salary, and city where the employee's maximum salary is greater than 40000 and group the results by designation.

Query:

 SELECT Name, City, MAX (Salary) AS Salary FROM employees GROUP BY Designation HAVING MAX (Salary) > 40000;  

SQL Queries

Basic Structure of SQL Queries

The basic structure of an SQL query consists of three clauses:

select, from, and where.

The query takes as its input the relations listed in the from clause, operates

on them as specified in the where and select clauses, and then produces a relation

as the result

Queries on a Single Relation

Let us consider a simple query using our university example, “Find the names

of all instructors.” Instructor names are found in the instructor relation, so we

put that relation in the from clause.

The instructor’s name appears in the name attribute, so we put that in the select clause.

​

select name from instructor;

SQL Queries

Basic Structure of SQL Queries

​

Example

​

Select all records from the Customers table:

SELECT * FROM Customers;

SQL Queries

Some of The Most Important SQL Commands

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

SQL Queries

-- select first_name and last_name columns from Customers table

​

SELECT first_name, last_name FROM Customers;

SQL Queries

SQL SELECT ALL

To select all columns from a database table, we use the * character. For example,

-- select all columns from Customers table

​

SELECT * FROM Customers;

SQL Queries

SQL SELECT WHERE Clause

A SELECT statement can have an optional WHERE clause. The WHERE clause allows us to fetch records from a database table that matches specified condition(s).

For example,

-- select all columns from the customers table with last_name 'Doe'

​

SELECT * FROM Customers WHERE last_name = 'Doe';

SQL Queries

SQL SELECT WHERE Clause

- select age and country columns from customers table where the country is 'USA'

​

SELECT age, country FROM Customers WHERE country = 'USA';

SQL Queries

SQL Operators

The WHERE clause uses operators to construct conditions. Some of the commonly used operators are:

​

1. Equal to Operator (=)

​

-- select all columns from Customers table with first name 'John'

SELECT * FROM Customers WHERE first_name = 'John';

SQL Queries

SQL Operators

​

2. Greater than (>)

​

-- select all columns from Customers table with age greater than 25

SELECT * FROM Customers WHERE age > 25;

The above SQL command selects all the customers from the Customers table whose age is greater than 25.

​

3. AND Operator (AND)

​

-- select all columns from Customers table with last_name 'Doe' and country 'USA'

SELECT * FROM Customers WHERE last_name = 'Doe' AND country = 'USA';

The above SQL command selects all the customers from the Customers table having last_name Doe and country USA.

SQL query

• SELECT Statement with HAVING Clause
• Consider the following database for HAVING Clause:
• Query:
• SELECT Department, sum(Salary) as Salary�FROM employee�GROUP BY department�HAVING SUM(Salary) >= 50000;
• Output:

​

SQL query

• SELECT Statement with ORDER BY clause in SQL
• In this example, we will use SELECT Statement with ORDER BY clause
• Query:
• SELECT * FROM Customer ORDER BY Age DESC;

​

Output:

SQL query

• SELECT Statement

​

Output:

SELECT s_id, name, age FROM student;

SQL query

• SELECT Statement

​

Output:

Select a particular record based on a condition

We can use the WHERE clause to set a condition,

​

SELECT * FROM student WHERE name = 'Abhi';

SQL query

• SELECT Statement

​

Output:

Let's write a simple SQL query to display the record for student with s_id as 101.

​

SELECT s_id,

name,

age,

address

FROM student WHERE s_id = 101;

SQL query

• SELECT Statement

​

Output:

SELECT s_id,

name,

age,

address

FROM student WHERE name = 'Adam';

SQL query

• SELECT Statement

​

Output:

SELECT * FROM Emp ORDER BY salary;

SELECT * FROM Emp ORDER BY salary DESC;

Output:

SQL Subquery�

• SQL | Subquery
• In SQL a Subquery can be simply defined as a query within another query. In other words we can say that a Subquery is a query that is embedded in WHERE clause of another SQL query. Important rules for Subqueries:
• You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.
• A subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.

​

​

​

​

​

​

​

​

​

�

SQL Subquery�

• SQL | Subquery

​

​

​

​

​

​

​

​

​

�

Sample Queries:

• To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE table whose section is A

SELECT NAME, LOCATION, PHONE_NUMBER �FROM DATABASE �WHERE ROLL_NO IN (SELECT ROLL_NO �FROM STUDENT �WHERE SECTION='A');

Explanation : First subquery executes “ SELECT ROLL_NO from STUDENT where SECTION=’A’ ” returns ROLL_NO from STUDENT table whose SECTION is ‘A’.Then outer-query executes it and return the NAME, LOCATION, PHONE_NUMBER from the DATABASE table of the student whose ROLL_NO is returned from inner subquery. Output:

SQL Subquery�

​

​

​

​

​

​

​

​

​

�

In a subquery, the outer query's result depends on the result set of the inner subquery. That's why subqueries are also called nested queries.

Here is how this code works:

executes the subquery first (inner query), and returns the minimum age 22

executes the outer query, and selects customers with age 22

�

SQL Subquery�

​

​

​

​

​

​

​

​

​

�

Example 1: This example uses the Greater than comparison operator with the Subquery.

�

�

�

SELECT * FROM Student_Details WHERE Stu_Marks> ( SELECT AVG(Stu_Marks ) FROM Student_Details);  

Joins

Types of Joins

• Inner Join

o Theta Join

o EQUI join:

o Natural Join (⋈)

• Outer Join

o Left Outer Join (A B)

o Right Outer Join (A B)

o Full Outer Join (A B)

Conditional Join

Joins

INNER JOIN is used to return rows from both tables which satisfy the given condition. It is the most widely used join operation and can be considered as a default join-type

An Inner join or equijoin is a comparator-based join which uses equality comparisons in the join-predicate. However, if you use other comparison operators like ">" it can't be called equijoin.

Inner Join further divided into three subtypes:

• Theta join

• Natural join

• EQUI join

Joins

SQL INNER JOIN combines two or more tables based on specified columns and retrieves records with matching values in the common columns.

Explanation

Let us look at an example scenario to have a better understanding.

Suppose we have the information of employees in a company divided between two tables namely EmpDetails and Marital status. Where,

EmpDetails table holds details like Employee ID, Name and Salary.

MaritalStatus table holds the details Employee ID, Age, and Marital Status.

​

When we perform the Inner Join operation on these two tables based on the join-predicate 

EmpDetails.EmpID = MaritalStatus.EmpID,

the resultant records hold the following info:

ID, Name, Salary, Age and, Status of the matched records.

Joins

SQL INNER JOIN 

table ORDERS

 table named CUSTOMERS

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