Structured Query Language Structured Query Language SQL stands

Structured Query Language

Structured Query Language • SQL stands for “Structured Query Language”. • Used for accessing and modifying information in the database. • Some of the SQL commands used in SQL programming are • SELECT Statement, • UPDATE Statement, • INSERT INTO Statement, • DELETE Statement, • WHERE Clause, • ORDER BY Clause, • GROUP BY Clause, etc.

Structured Query Language • SQL commands are instructions used to communicate with the database to perform specific task that work with data. • SQL commands are grouped into four major categories • Data Definition Language (DDL) • Data Manipulation Language (DML) • Transaction Control Language (TCL) • Data Control Language (DCL) •

Structured Query Language • Data Definition Language (DDL) – • These SQL commands are used for creating, modifying, and dropping the structure of database objects. • The commands are • CREATE, • ALTER, • DROP, • RENAME, and • TRUNCATE.

Structured Query Language • Data Manipulation Language (DML) – • These SQL commands are used for storing, retrieving, modifying, and deleting data. • These commands are • SELECT, • INSERT, • UPDATE, and • DELETE.

Structured Query Language • Transaction Control Language (TCL) – • These SQL commands are used for managing changes affecting the data. • These commands are • COMMIT, • ROLLBACK, and • SAVEPOINT. • Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Structured Query Language • Some basic Data Types • NUMBER used to store integer numbers, floating point numbers. • DECIMAL used to store numbers • CHAR(n) used to store characters of fixed length, n is the no. of characters. • VARCHAR(n) used to store characters of varying length. • DATE used for date values • TIME used for time values and has at least 8 positions with components hh: mm: ss.

Structured Query Language • DDL Command • The CREATE TABLE Statement • CREATE TABLE statement is used to create a table in a database. • Syntax CREATE TABLE table_name ( column_name 1 data_type, column_name 2 data_type, column_name 3 data_type, . . ); • Eg) CREATE TABLE customer(First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date); • DESC customer; : - will display the structure of the table

Structured Query Language • Question: • Create a Table ‘student’ with following fields CREATE TABLE STUDENT (SID CHAR(20), FIRST_NAME CHAR(20), LAST_NAME CHAR(20), ADDRESS CHAR(20), PHONE CHAR(10), LOGIN CHAR(20), PASSWORD CHAR(20)); DESCRIBE STUDENT;

Structured Query Language • DDL Command • The CREATE TABLE AS Statement • create a table from an existing table by copying the existing table's columns. • SYNTAX - COPYING ALL COLUMNS FROM ANOTHER TABLE • CREATE TABLE new_table AS (SELECT * FROM old_table); If there were records in the OLD table, then the NEW table would also contain the records selected by the SELECT statement.

Structured Query Language • The CREATE TABLE AS Statement • Question: • Create a new table TEMP from TEST 1, with all date and colums • Table test 1 CREATE TABLE TEMP AS (SELECT * FROM TEST 1); SELECT * FROM TEMP;

Structured Query Language • DDL Command • The CREATE TABLE AS Statement • SYNTAX - COPYING SELECTED COLUMNS FROM ANOTHER TABLE • CREATE TABLE new_table AS (SELECT column_1, column 2, . . . column_n FROM old_table);

Structured Query Language • The CREATE TABLE AS Statement • Question: • Create a new table TEMP 1 from TEST 1, with 1 colums NAME • Table test 1 CREATE TABLE TEMP 1 AS (SELECT NAME FROM TEST 1); SELECT * FROM TEMP;

Structured Query Language • The CREATE TABLE AS Statement • Question: • Create a new table TEMP 2 from TEST 1, with all column and condition age >= 33 • Table test 1 CREATE TABLE TEMP 2 AS (SELECT * FROM TEST 1 WHERE AGE >=33); SELECT * FROM TEMP 2;

Structured Query Language • DDL Command • The CREATE TABLE AS Statement • SYNTAX - COPYING SELECTED COLUMNS FROM MULTIPLE TABLES • CREATE TABLE new_table AS (SELECT TABLE 1. column_1, . . TABLE 2. colum 1, . . FROM old_table_1, old_table_2, . . . old_table_n);

Structured Query Language • The CREATE TABLE AS Statement • Question: • Create a new table TEMP 3 from TEST 1 and TEST 2 , with columns NAME from TEST 1 & SALARY from TEST 2 • With condition NAME in TEST 1= NAME_S in TEST 2 • Table test 1 Table test 2 CREATE TABLE TEMP 3 AS (SELECT TEST 1. NAME, TEST 2. SALARY FROM TEST 1, TEST 2 WHERE TEST 1. NAME=TEST 2. NAME_S); SELECT * FROM TEMP 3;

Structured Query Language • DDL Command • The CREATE TABLE AS Statement • SYNTAX -create a table from another table without copying any values from the old table • CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2);

Structured Query Language • DDL Command - ALTER • The ALTER TABLE Statement • ALTER TABLE statement is used to add, delete, or modify columns in an existing table. • To add a column in a table • syntax: • ALTER TABLE table_name ADD column_name datatype; • Eg) ALTER TABLE customer ADD Gender char(1);

Structured Query Language • Question: • Add a new column in the ‘student’ table as ‘GENDER’ CHAR(1) ALTER TABLE STUDENT ADD GENDER CHAR (1); DESC STUDENT;

Structured Query Language • • DDL Command - ALTER To delete a column in a table, syntax: ALTER TABLE table_name DROP COLUMN column_name; • • • Eg)ALTER table customer drop column Birth_Date; To change the data type of a column in a table, syntax: ALTER TABLE table_name MODIFY column_name datatype Eg) ALTER TABLE customer MODIFY Address char(100);

Structured Query Language • Question: • Delete the colunm ‘PHONE’ from ‘student’ table ALTER TABLE STUDENT DROP COLUMN PHONE; DESC STUDENT;

Structured Query Language • Question: • Modify the length of ‘ADDRESS’ to 50 in the ‘student’ table ALTER TABLE STUDENT MODIFY ADDRESS CHAR(50); DESC STUDENT;

Structured Query Language DDL Command - ALTER To rename a column in a table, syntax: ALTER TABLE table_name RENAME COLUMN column 1 TO column 2; • Eg) ALTER table customer RENAME COLUMN Address TO Addr; • •

Structured Query Language • Question: • Rename the column ‘FIRST_NAME’ to ‘S_NAME’ in the ‘student’ table ALTER TABLE STUDENT RENAME COLUMN FIRST_NAME TO S_NAME; DESC STUDENT;

Structured Query Language • DDL Command - Drop • The DROP TABLE Statement • The DROP TABLE statement is used to delete a table. • DROP TABLE table_name; DROP TABLE STUDENT;

Structured Query Language • DDL Command • The TRUNCATE TABLE Statement • If we only want to delete the data inside the table, and not the table itself then, use the TRUNCATE TABLE statement: • TRUNCATE TABLE table_name TRUNCATE TABLE STUDEN T;

Structured Query Language • DDL Command • The RENAME Statement • The SQL RENAME command is used to change the name of the table. • Syntax to rename a table • RENAME old_table_name To new_table_name; • Rename the ‘student ‘ table to ‘MCASTUDENTs’ RENAME STUDENT TO MACSTUDENTS;

Structured Query Language DML Command The INSERT INTO Statement INSERT INTO statement is used to insert a new row in a table. It is possible to write the INSERT INTO statement in two forms. The first form doesn't specify the column names where the data will be inserted, only their values: • INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) • The second form specifies both the column names and the values to be inserted: • • • INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . )

Structured Query Language • Question: • Insert sid, first_name, last_name, address, phone, login and password in the student table • Does not specify the column name INSERT INTO STUDENT VALUES('S 100', 'APPU', 'KUTTAN', 'ADDRESS', '9995208899', 'APPUS', 'SUTTAN'); SELECT * FROM STUDENT;

Structured Query Language • Question: • Insert sid, first_name, last_name, and phone in the student table INSERT INTO STUDENT (SID, FIRST_NAME, LAST_NAME, PHONE) VALUES('S 102', 'KUKKU', 'KOO', '99999'); SELECT * FROM STUDENT;

Structured Query Language DML Command The UPDATE statement Used to update existing records in a table. UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value • Eg)UPDATE Store_Information SET Sales = 500 WHERE store_name = "Los Angeles" AND Date = "Jan-08 -1999" • •

Structured Query Language • Question: • Change the SID of KUKKU to ‘S 101’ in the student table UPDATE STUDENT SET SID='S 101' WHERE FIRST_NAME='KUKKU'; SELECT * FROM STUDENT;

Structured Query Language • DML Command • The SELECT Statement • The SELECT statement is used to select data from a database. • SELECT column_name(s) FROM table_name & • SELECT * FROM table_name • The WHERE clause is used to extract only those records that fulfill a specified criterion. • SELECT column_name(s) FROM table_name WHERE column_name operator value

Structured Query Language • Question: • Display all Sid, First_name and Phone from the student table SELECT SID, FIRST_NAME, PHONE FROM STUDENT;

Structured Query Language DML Command The DELETE Statement The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column=some_value • Eg)DELETE FROM Store_Information WHERE store_name = "Los Angeles" • It is possible to delete all rows in a table without deleting the table. • DELETE FROM table_name or DELETE * FROM table_name • •

Structured Query Language • Question: • Delete the record where SID =‘S 102’ from the student table DELETE from student where SID='S 102'; SELECT * FROM STUDENT;

Structured Query Language • Where clause • Used to specify a condition while fetching the data from single table or joining with multiple tables. • Used to filter the records and fetching only necessary records. • It can used in SELECT, UPDATE, DELETE statement, etc. , • Syntax: • SELECT column 1, column 2, column. N FROM table_name WHERE [condition] • Eg • SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000;

Structured Query Language • AND Operator: • The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. • Syntax: • SELECT column 1, column 2, column. N FROM table_name WHERE [condition 1] AND [condition 2]. . . AND [condition. N]; • SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;

Structured Query Language • Question: • List all details from the TAB 1 table where S_Name=“saji” & weight>10 • TAB 1 SELECT * FROM TAB 1 WHERE S_NAME='SAJI' AND WEIGHT>10;

Structured Query Language • OR Operator: • The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. • SELECT column 1, column 2, column. N FROM table_name WHERE [condition 1] OR [condition 2]. • SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 OR age < 25;

Structured Query Language • Question: • List all details from the TAB 1 table where S_NAME=“saji” OR weight>50 • TAB 1 SELECT * FROM TAB 1 WHERE S_NAME=‘SAJI’ OR WEIGHT>50;

Structured Query Language • "AND" & "OR Operator: • SELECT * FROM suppliers WHERE (city = 'New York' AND name = 'IBM') OR (ranking >= 10);

Structured Query Language • Question: • List all details from the TAB 1 table where ROLL_NO>101 AND ROLL_NO<103 OR weight>50 • TAB 1 SELECT * FROM TAB 1 WHERE (ROLL_NO>101 AND ROLL_NO<103) OR WEIGHT>50;

Structured Query Language • IN • SELECT * FROM STUDENT WHERE FIRST_NAME IN ('APPU', 'RAJI', 'SAJAN'); • This statement will select the all columns from the student table where the first_name is equal to either: APPU, RAJI or SAJAN. It will return the rows if it is ANY of these values.

Structured Query Language • IN • The IN conditional operator can be rewritten by using compound conditions using the equals operator and combining it with OR • SELECT * FROM STUDENT WHERE FIRST_NAME ='APPU' OR FIRST_NAME='RAJI' OR FIRST_NAME= 'SAJAN'; • Same o/p

Structured Query Language • BETWEEN NUMERIC • The BETWEEN conditional operator is used to test to see whether or not a value (stated before the keyword BETWEEN) is "between" the two values stated after the keyword BETWEEN. • SELECT * FROM STUDENT WHERE AGE BETWEEN 20 AND 30;

Structured Query Language • BETWEEN NUMERIC • QUESTION: • Select all records from TEST 2 table where age >=40 & <=50 • Table test 2 SELECT * FROM TEST 2 WHERE SALARY BETWEEN 40 AND 50;

Structured Query Language • BETWEEN DATE • QUESTION: • Select all records from TEMP table where DOB >=‘ 02 -JAN-14’ AND <=’ 12 -MAR-14’ • Table TEMP SELECT * FROM TEMP WHERE DOB BETWEEN '02 -JAN-14' AND '12 MAR-14';

Structured Query Language • BETWEEN USING NOT OPERATOR • QUESTION: • Select all records from TEST 2 table where age NOT >=40 & <=50 • Table test 2 SELECT * FROM TEST 2 WHERE SALARY NOT BETWEEN 40 AND 50;

Structured Query Language • Constraints in SQL • Constraints will be given to columns at the time of table creation • 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.

Structured Query Language • Constraints • NOT NULL Constraint: Ensures that a column cannot have NULL value. • Example: • The following SQL creates a new table called TAB 1 and adds 3 columns, 2 of which, ID and NAME, specify not to accept NULLs: • CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT); • If the table has already been created, then to add a NOT NULL constraint to AGE column • ALTER TABLE TAB 1 MODIFY AGE INT NOT NULL;

Structured Query Language • Constraints • DEFAULT Constraint: Provides a default value for a column when none is specified. • Example: • following SQL creates a new table called TAB 1 and adds 3 columns. • SALARY column is set to 5000. 00 by default, so in case INSERT INTO statement does not provide a value for this column, then by default this column would be set to 5000. • CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, SALARY DECIMAL(10, 2) DEFAULT 5000. 00);

Structured Query Language • Constraints • UNIQUE Constraint: Ensures that all values in a column are different. • Example: • following SQL creates a new table called TAB 1 and adds 3 columns. • AGE column is set to UNIQUE, so that can not have two records with same age: • CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE);

Structured Query Language • Constraints • PRIMARY Key: Uniquely identified each rows/records in a database table. • Primary keys must contain unique values. • A primary key column cannot have NULL values. • A table can have only one primary key, which may consist of single or multiple fields. • When multiple fields are used as a primary key, they are called a composite key.

Structured Query Language • Create Primary Key: • Here is the syntax to define ID attribute as a primary key in a TAB 1 table. • CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR(20)NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID));

Structured Query Language • Constraints • FOREIGN Key: Uniquely identified a rows/records in any another table. • A foreign key is a key used to link two tables together. • This is sometimes called a referencing key. • The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

Structured Query Language • • Example: Consider the structure of the two tables as follows: TAB 1 table: CREATE TABLE TAB 1( ID INT NOT NULL, NAME VARCHAR(20)NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID)); • TAB 2 table: • CREATE TABLE TAB 2( ID INT NOT NULL, SID INT REFERENCES TAB 1(ID), SALARY DECIMAL(10, 2)NOT NULL, PRIMARY KEY (ID));

Structured Query Language • Constraints • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. • Example: • following SQL creates a new table called TAB 1 and adds 3 columns. • CHECK with AGE column, so that you can not have any VALUE below 10 years: • CREATE TABLE TAB 1( ID INT NOT NULL, NAME CHAR(20) NOT NULL, AGE INT NOT NULL CHECK(AGE>10), PRIMARY KEY (ID));

Structured Query Language • Constraints • INDEX: Use to create and retrieve data from the database very quickly. • Index can be created by usingle or group of columns in a table. • Example: • Following SQL creates a new table called TAB 1 and adds 3 columns: • CREATE TABLE TAB 1( ID INT NOT NULL, NAME CHAR(20) NOT NULL, AGE INT NOT NULL CHECK(AGE>10), PRIMARY KEY (ID));

Structured Query Language • To create index on single or multiple columns using the following syntax: • CREATE INDEX index_name ON table_name ( column 1, column 2. . . ); • To create an INDEX on AGE column • SQL syntax: • CREATE INDEX TAB 1_age ON TAB 1 ( AGE );

Structured Query Language • ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. • Syntax: • SELECT column-list FROM table_name [WHERE condition] [ORDER BY column 1, column 2, . . column. N] [ASC | DESC]; • Following is an example, which would sort the result in ascending order by NAME • SQL> SELECT * FROM TAB 1 ORDER BY NAME; • Following is an example, which would sort the result in descending order by NAME: • SQL> SELECT * FROM TAB 1 ORDER BY NAME DESC;

Structured Query Language • DISTINCT keyword is used with SELECT statement to eliminate all the duplicate records and fetching only unique records. • Syntax: • SELECT DISTINCT column 1, column 2, . . . column. N FROM table_name WHERE [condition] • Example • SELECT DISTINCT SALARY FROM TAB 1 ORDER BY SALARY;

Structured Query Language • LIKE clause is used to compare a value to similar values using wildcard operators. • There are two wildcards used in conjunction with the LIKE operator: • The percent sign (%) • The underscore (_) • The percent sign represents zero, one, or multiple characters. • The underscore represents a single number or character.

Structured Query Language • Example: • WHERE SALARY LIKE '200%‘ Finds any values that start with 200 • WHERE SALARY LIKE '%200%‘ Finds any values that have 200 in any position • WHERE SALARY LIKE '_00%‘ Finds any values that have 00 in the second and third positions • WHERE SALARY LIKE '%2'Finds any values that end with 2

Structured Query Language • GROUP BY clause • Used to arrange identical data into groups. • Syntax: • SELECT column 1, column 2 FROM table_name GROUP BY column 1, column 2;

Structured Query Language • GROUP BY clause • Consider the CUSTOMERS table is having the following records: • to know the total amount of salary on each customer, then GROUP BY query would be as follows: • SELECT NAME, SUM(SALARY) FROM CUSTOMER GROUP BY NAME;

Structured Query Language • • Aggregate functions Provide mathematical operations. count() - counts a number of rows sum() - compute sum avg() - compute average min() - compute minimum max() - compute maximum

Structured Query Language • Aggregate functions • SQL COUNT Function - Used to count the number of rows in a database table. • To count total number of rows in this table, then • SELECT COUNT(*) FROM TAB 1 ; • want to count the number of records for AJI, then • SELECT COUNT(*) FROM TAB 1 WHERE name=“AJI";

Structured Query Language • Aggregate functions • SQL MAX Function To select the highest (maximum) value for a certain column. • to fetch maximum value of AGE, • SELECT MAX(AGE) FROM TAB 1; • SQL MIN Function - to select the lowest (minimum) value for a certain column. • to fetch minimum value of AGE • SELECT MIN(AGE) FROM TAB 1;

Structured Query Language • Aggregate functions • SQL AVG Function - selects the average value for certain table column. • SELECT AVG(AGE) FROM TAB 1; • SQL SUM Function - selecting the total for a numeric column. • SELECT SUM(AGE) FROM TAB 1; • To take sum of various records set using GROUP BY clause. • Following example will sum up all the records related to a single person and you will have total DEPOSIT by every person. • SELECT NAME, SUM(DEPOSIT) FROM BANK GROUP BY NAME;

Structured Query Language • Aggregate functions • SQL SQRT Functions - This is used to generate a square root of a given number. • SELECT name, SQRT(AGE) FROM TAB 1; • SQL UPPER Functions - converts the value of a field to uppercase • SELECT UPPER(FIRST_NAME) FROM STUDENT;

Structured Query Language • Aggregate functions • SQL LOWER Functions - converts the value of a field to LOWERCASE • SELECT LOWER(FIRST_NAME) FROM STUDENT;

Structured Query Language • set operators • SQL set operators allows combine results from two or more SELECT statements. • They are • UNION ALL • INTERSECT • MINUS (EXCEPT)

Structured Query Language • set operators • Union Clause • UNION clause/operator is used to combine the resultset of two or more SELECT statements • SELECT statement within the UNION must have the same number of columns. With same data type • UNION operator selects only distinct values by default. • syntax • SELECT column_name(s) FROM table 1 UNION SELECT column_name(s) FROM table 2;

Structured Query Language • set operators • Union Clause • To allow duplicate values, use the ALL keyword with UNION. • Syntax • SELECT column_name(s) FROM table 1 UNION ALL SELECT column_name(s) FROM table 2;

Structured Query Language • set operators Union clause • Table test 1 • Table test 2 SELECT NAME FROM TEST 1 UNION SELECT NAME FROM TEST 2;

Structured Query Language • set operators UNION ALL clause • Table test 1 Table test 2 SELECT NAME FROM TEST 1 UNION ALL SELECT NAME FROM TEST 2;

Structured Query Language • set operators • INTERSECT • Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. • Syntax • SELECT column_name(s) FROM table 1 INTERSECT SELECT column_name(s) FROM table 2;

Structured Query Language • set operators INTERSECT • Table test 1 Table test 2 SELECT NAME FROM TEST 1 INTERSECT SELECT NAME_S FROM TEST 2;

Structured Query Language • set operators • MINUS • returns any distinct values from the left query that are not also found on the right query. • select * from test 1 where name='AJI' • MINUS • select * from test 2 where name_s='AJI'

Structured Query Language • set operators MINUS • Table test 1 Table test 2 SELECT NAME FROM TEST 1 MINUS SELECT NAME_S FROM TEST 2;

Structured Query Language • JOIN • Used to combine rows from two or more tables • A JOIN is a means for combining fields from two tables by using values common to each.

Structured Query Language • JOIN • Table test 1 Table test 2 SELECT NAME, SEX FROM TEST 1, TEST 2 WHERE TEST 1. NAME=TEST 2. NAME_S;

Structured Query Language • JOIN • Need at least one common field and have a relationship between them • The are two types of SQL JOINS - EQUI JOIN and NON EQUI JOIN • 1) SQL EQUI JOIN : • The SQL EQUI JOIN is a simple sql join uses the equal sign(=) as the comparison operator for the condition. • It has two types - SQL Outer join and SQL Inner join. • 2) SQL NON EQUI JOIN : • The SQL NON EQUI JOIN is a join uses comparison operator other than the equal sign like >, <, >=, <= with the condition.

Structured Query Language SQL EQUI JOIN can be classified into two types – INNER JOIN and OUTER JOIN 1. SQL INNER JOIN This type of EQUI JOIN returns all rows from tables where the key record of one table is equal to the key records of another table. • 2. SQL OUTER JOIN • This type of EQUI JOIN returns all rows from one table and only those rows from the secondary table where the joined condition is satisfying i. e. the columns are equal in both tables. • •

Structured Query Language • JOIN

Structured Query Language • INNER JOIN: Returns rows when there is a match in both tables. • When the join-condition is satisfied, column values for each matched pair of rows of A and B are combined into a result row. • Syntax: • SELECT table 1. column 1, table 2. column 2. . . FROM table 1 INNER JOIN table 2 ON table 1. common_field = table 2. common_field;

Structured Query Language • TAB 1 TAB 2 SELECT ID FROM TAB 1 INNER JOIN TAB 2 ON TAB 1. ID=TAB 2. SID;

Structured Query Language • NATURAL JOIN • Type of EQUI JOIN and is structured in such a way that, columns with same name of associate tables will appear once only • The associated tables have one or more pairs of identically named columns. • The columns must be the same data type. • Don’t use ON clause in a natural join. • Syntax • Select * FROM table 1 NATURAL JOIN table 2;

Structured Query Language • NATURAL JOIN • SELECT * FROM foods NATURAL JOIN company;

Structured Query Language • OUTER JOIN • Returns all rows from both the tables which satisfy the join condition along with rows which do not satisfy the join condition. • They are • LEFT OUTER JOIN or LEFT JOIN • RIGHT OUTER JOIN or RIGHT JOIN • FULL OUTER JOIN

Structured Query Language • LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table. • joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table before the JOIN clause • Syntax: • Select * FROM table 1 LEFT OUTER JOIN table 2 ON table 1. column_name=table 2. column_name;

Structured Query Language • LEFT JOIN: • SQL LEFT join fetches a complete set of records from table 1, with the matching records (depending upon the availability) in table 2. • The result is NULL in the right side when no matching will take place.

Structured Query Language • LEFT JOIN • TAB 1 TAB 2 SELECT * FROM TAB 1 LEFT OUTER JOIN TAB 2 ON TAB 1. ID=TAB 2. SID

Structured Query Language • RIGHT JOIN • joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table written after the JOIN clause • Syntax • Select * FROM table 1 RIGHT OUTER JOIN table 2 ON table 1. column_name=table 2. column_name ;

Structured Query Language • RIGHT • join fetches a complete set of records from table 2, i. e. the rightmost table after JOIN clause, with the matching records (depending upon the availability) in table 1. • The result is NULL in the left side when no matching will take place.

Structured Query Language • RIGHT JOIN • TAB 1 TAB 2 SELECT * FROM TAB 1 RIGHT OUTER JOIN TAB 2 ON TAB 1. ID=TAB 2. SID

Structured Query Language • FULL OUTER JOIN • combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. • Syntax • SELECT * FROM table 1 FULL OUTER JOIN table 2 ON table 1. column_name=table 2. column_name ;

Structured Query Language • FULL OUTER JOIN • TAB 1 TAB 2 SELECT * FROM TAB 1 FULL OUTER JOIN TAB 2 ON TAB 1. ID=TAB 2. SID

Structured Query Language • Alias • Rename a table or a column temporarily by giving another name known as alias. • The column aliases are used to rename a table's columns for the purpose of a particular SQL query. • SELECT column_name AS alias_name FROM table_name WHERE [condition];

Structured Query Language • Alias • Table test 1 SELECT NAME AS PERSON, AGE AS OLD FROM TEST 1;

Structured Query Language • A SELF JOIN • which is used to join a table to itself • In this join, the table appears twice after the FROM clause and is followed by aliases for the tables that qualify column names in the join condition • In this join those rows are returned from the table which are satisfying the conditions.

Structured Query Language • SELF JOIN • TAB 1 TAB 2 SELECT A. ID, B. ID FROM TAB 1 A, TAB 1 B WHERE A. ID=B. ID;

Structured Query Language • Joining table through referential-integrity • Referential integrity is made up by the combination of a primary key and a foreign key.

Structured Query Language • Joining table through referential-integrity • Display rollno, name and marks • TAB 1 TAB 2 SELECT TAB 1. ROLLNO, TAB 1. NAME, TAB 2. MARK FROM TAB 1, TAB 2 WHERE TAB 1. ROLLNO=TAB 2. ROLLNO;

Structured Query Language • Joining table through referential-integrity • Display rollno, name and marks whose marks >40 • TAB 1 TAB 2 SELECT TAB 1. ROLLNO, TAB 1. NAME, TAB 2. MARK FROM TAB 1, TAB 2 WHERE TAB 1. ROLLNO=TAB 2. ROLLNO AND TAB 2. MARK >40

Structured Query Language • Joining table with group by and order by • Display rollno, name and total subject marks of each student • TAB 1 TAB 2 SELECT TAB 1. ROLLNO, TAB 1. NAME, SUM(TAB 2. MARK) FROM TAB 1, TAB 2 WHERE TAB 1. ROLLNO=TAB 2. ROLLNO GROUP BY TAB 1. ROLLNO, TAB 1. NAME ORDER BY TAB 1. ROLLNO;