SQL commands in database managemant systems

introduction to sql commands in database management systems

1. School of Computing Science and Engineering Program: B.Tech Course Code: E2UC302B Course Name: Database Management System Dr.Manikant Panthi

2. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: D BMS Database Management System A Database Management System (DBMS) is a collection of database and stored procedures. A DBMS enables you to store, extract and manage important information from a database. It is software that is used to maintain data security and data integrity in a structured database. Program Name: B.Tech Program Code: E2UC302B

3. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: D BMS Database Management System DBMS helps in maintaining and retrieving data in different form. There are various tools available for DBMS: -Oracle -INGRES -Sybase -Microsoft SQL Server -Ms-Access -IBM-DB-II -My SQL Program Name: B.Tech Program Code: E2UC302B

4. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: D BMS Application of DBMS -Banking: For account holder information, amount with draw and deposit, load and other transactions -Airlines: For reservations, cancelation, fare detail and airline schedules -Universities: For student registration, examination, fee detail, course detail and other information. -Manufacturing: For inventory, production, sale and purchase orders -Human Resources: Employee records, salaries, tax deductions, allowances -Multimedia application -Graphical Information System (GIS) Program Name: B.Tech Program Code: E2UC302B

5. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: D BMS Introduction to SQL Structured Query Language or SQL is a standard computer language for accessing and manipulating database systems. SQL comprises one of the fundamental building blocks of modern database architecture. SQL defines methods using which user can create and manipulate databases on all major platforms. SQL is a procedural language like C or BASIC. Program Name: B.Tech Program Code: E2UC302B

6. School of Computing Science and Engineering Types of SQL Commands SQL Categorizes its commands on the basis of functionalities performed by them. There are five types of SQL Commands which can be depicted below: Program Name: B.Tech Program Code: E2UC302B Data Definition Language Data Manipulation Language Transaction Control Language Data Control Language Data query Language

7. School of Computing Science and Engineering SQL Data Types Program Name: B.Tech Program Code: E2UC302B Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table, if we want to store a string type of data then we will have to declare a string data type of this column. Data types mainly classified into three categories for every database. String Data types Numeric Data types Date and time Data types

8. School of Computing Science and Engineering String data t ypes Program Name: B.Tech Program Code: E2UC302B

9. School of Computing Science and Engineering Numeric data t ypes Program Name: B.Tech Program Code: E2UC302B

10. School of Computing Science and Engineering Date and Time data Types Program Name: B.Tech Program Code: E2UC302B

11. School of Computing Science and Engineering S QL Operators Program Name: B.Tech Program Code: E2UC302B

12. DML and DDL Commands in SQL DDL stands for Data Definition Language and refers to SQL commands used to create, modify, and delete database structures such as tables, indexes, and views. DML stands for Data Manipulation Language and refers to SQL commands used to insert, update, and delete data within a database.

13. Data Definition Language DDL (Data Definition Language) is a type of SQL command used to define data structures and modify data. It creates, alters, and deletes database objects such as tables, views, indexes, and users. Examples of DDL statements include CREATE, ALTER,  DROP and TRUNCATE .

14. Data Definition Language (DDL) CREATE : It is used to create objects in the database, such as tables, views, stored procedures, and more. ALTER : It is used to modify the structure of an existing database object. DROP : It is used to delete an entire object or part of an object from the database. TRUNCATE : Used to delete all records from a table but does not delete the table structure. RENAME : Used to rename an existing database object. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

15. Data Definition Language (DDL)

16. SQL Rename Data Base D atabase users and administrators want to change the name of the database for some technical reasons. So, the  Rename Database  statement in SQL is used to change the name of the existing database. Rename Database statement is used because the developers think that the original name is not more relevant to the data of the database, or they want to give a temporary name to that database. Syntax of Rename Database in SQL ALTER DATABASE old_database_name MODIFY NAME = new_database_name ; Syntax of Rename Database in MySQL RENAME DATABASE old_database_name TO new_database_name ; This syntax is used when we want to change the name of the database in MySQL.

17. SQL Rename Data Base

18. SQL SELECT Database

19. COPY TABLE If you want to copy the data of one SQL table into another SQL table in the same SQL server, then it is possible by using the SELECT INTO statement in SQL. The SELECT INTO statement in Structured Query Language copies the content from one existing table into the new table. SQL creates the new table by using the structure of the existing table.

20. COPY TABLE Example 1:  In this example, we have a table called  Cars  with three columns: Suppose you want to copy the content of the above Car table into the new table  Car_Details .  For this, you have to type the following query in SQL: SELECT * INTO  Car_Details  FROM Cars;    Let's check the  Car_Details  table is created successfully or not in the database: SELECT * FROM  Car_Details ;   

21. COPY TABLE

22. COPY TABLE  In this example, we have a table called  Employee  with four columns:

23. COPY TABLE Suppose we want to copy only the record of those employees whose Salary is more than 40,000. For this, we have to type the following query in SQL: SELECT * INTO Emp_Salary_40000 FROM Cars WHERE  Emp_Salary   >  40000;   Let's check the  Emp_Salary_40000  table created successfully or not in the database: SELECT * FROM Emp_Salary_40000;  

24. ALTER Table Command The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. ALTER TABLE  table_name       ADD (column_Name1 column-definition,     column_Name2 column-definition,     .....     column_NameN  column-definition);    

25. ALTER Table Command The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table. ALTER TABLE  table_name       ADD (column_Name1 column-definition,     column_Name2 column-definition,     .....     column_NameN  column-definition);    

26. ALTER Table Command ALTER TABLE Cars ADD  Car_Model  Varchar(20);  

27. ALTER Table Command Suppose, you want to modify the datatypes of two columns  Emp_ContactNo .  and  Emp_EmailID  of the above Employee table. For this, you have to type the following query in the SQL: ALTER TABLE Employee ADD (  Emp_ContactNo . Int,  Emp_EmailID  varchar(80) ; 

28. ALTER TABLE DROP Column statement in SQL Syntax of ALTER TABLE DROP Column statement in SQL ALTER TABLE  table_name  DROP Column  column_name  ;   ALTER TABLE Cars DROP COLUMN  Car_Color  ;   SELECT * FROM Cars;  

29. Data Manipulation Language DML (Data Manipulation Language) is a type of SQL command used to manipulate data in a database. It inserts, updates, and deletes data from a database table. Examples of DML statements include INSERT, UPDATE, and DELETE.

30. Data Manipulation Language (DML) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B INSERT : Used to add new records to a database table. UPDATE : Used to modify existing records in a database table. DELETE : Used to delete existing records from a database table. MERGE : Used to combine data from two or more tables into one. SELECT : Used to retrieve data from one or more tables in a database.

31. Data Manipulation Language (DML) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

32. Data Manipulation Language (DML) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B mysql > USE  dbs ;   mysql >  CREATE   TABLE  student(ID  INT ,  Name   VARCHAR (20), Percentage  INT , Location  VARCHAR (20),  DateOfBirth   DATE );  

33. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B mysql >  SELECT  * FROM  student;  

34. SQL DELETE TABLE The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition. DELETE FROM table_name [WHERE condition]; But if you do not specify the WHERE condition it will remove all the rows from the table. DELETE FROM table_name ;

35. SQL Delete/Truncate Table

36. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL UPDATE The SQL commands ( UPDATE  and  DELETE ) are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause. SQL UPDATE  statement is used to change the data of the records held by tables. Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause. The UPDATE statement can be written in following form: UPDATE  table_name   SET  [column_name1= value1,...  column_nameN  =  valueN ] [ WHERE  condition]   Updating Multiple Fields: If you are going to update multiple fields, you should separate each field assignment with a comma.

37. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL UPDATE

38. Data Control Language (DCL) DCL, or Data Control Language, is a subset of SQL used to manage database security and access control. DCL commands determine who can access the database and what actions they can perform. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B The GRANT command is used to grant specific privileges to database users or roles: The REVOKE command is used to revoke previously granted privileges:

39. Data Control Language (DCL) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

40. Transaction Control Language (TCL) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B TCL, or Transaction Control Language, is a subset of SQL used to manage database transactions. TCL commands ensure data integrity by allowing you to control when changes to the database are saved permanently or rolled back.

41. Data Query Language (DQL) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B What is DQL? Data Query Language (DQL) is a critical subset of SQL (Structured Query Language) used primarily for querying and retrieving data from a database. While SQL encompasses a range of commands for data manipulation, DQL commands are focused exclusively on data retrieval. Data Query Language (DQL) forms the foundation of SQL and is indispensable for retrieving and analyzing data from relational databases. With a solid understanding of DQL commands and concepts, you can extract valuable insights and generate reports that drive informed decision-making. Whether you’re a database administrator, data analyst, or software developer, mastering DQL is essential for effectively working with databases.

42. Data Query Language (DQL) School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Purpose of DQL The primary purpose of DQL is to allow users to extract meaningful information from a database. Whether you need to retrieve specific records, filter data based on certain conditions, or aggregate and sort results, DQL plays a crucial role in various database-related tasks, including: Generating reports Extracting statistical information Displaying data to users Answering complex business queries

43. SQL Select Statement School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B The SELECT statement is the most commonly used command in Structured Query Language. It is used to access the records from one or more database tables and views. Syntax of SELECT Statement in SQL: SELECT  Column_Name_1, Column_Name_2, .....,  Column_Name_N   FROM   Table_Name ;  

44. SQL Select Statement School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Use the following query to create the Student_Records table in SQL: CREATE  TABLE   Student_Records     (   Student_Id   Int   PRIMARY   KEY ,     First_Name   VARCHAR  (20),     Address  VARCHAR  (20),     Age  Int   NOT   NULL ,   Percentage  Int   NOT   NULL ,   Grade  VARCHAR  (10)    ) ;  

45. SQL Select Statement School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

46. SELECT  Student_Id , Age, Percentage, Grade  FROM  Employee;   SELECT Statement with WHERE clause The WHERE clause is used with SELECT statement to return only those rows from the table, which satisfy the specified condition in the query. Syntax of SELECT Statement with WHERE clause SELECT  *  FROM   Name_of_Table   WHERE  [condition];  

47. The following query shows the record of those employees from the above table whose Emp_Panelty is 500: SELECT * FROM Employee_Details WHERE Emp_Panelty = 500; This SELECT query displays the following table in result:

48. SQL SELECT Statement with GROUP BY clause: The GROUP BY clause is used with the SELECT statement to show the common data of the column from the table: Syntax of SELECT Statement with GROUP BY clause SELECT column_Name_1, column_Name_2, ....., column_Name_N aggregate_function_name (column_Name2) FROM table_name GROUP BY column_Name1; Use the following query to create the Cars_Details table:

49. SQL SELECT Statement with GROUP BY clause:

50. SQL SELECT Statement with HAVING clause The HAVING clause in the SELECT statement creates a selection in those groups which are defined by the GROUP BY clause . SELECT column_Name_1, column_Name_2, .....,  column_Name_N   aggregate_function_name (column_Name_2)  FROM   table_name   GROUP   BY column_Name1  HAVING  ;   The following query shows the total salary of those employees having more than 5000 from the above Employee_Having table: SELECT SUM ( Employee_Salary ), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM( Employee_Salary )>50000;

51. SQL SELECT Statement with HAVING clause The following query shows the total salary of those employees having more than 50000 from the above Employee_Having table: SELECT SUM ( Employee_Salary ), Employee_City FROM Employee_Having GROUP BY Employee_City HAVING SUM( Employee_Salary )>50000;

52. SELECT Statement with ORDER BY clause The ORDER BY clause with the SQL SELECT statement shows the records or rows in a sorted manner. The ORDER BY clause arranges the values in both ascending and descending order. Few database systems arrange the values of column in ascending order by default

53. SQL KEYS: Primary Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B A column or columns is called primary key (PK) that uniquely identifies each row in the table. If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table. When multiple columns are used as a primary key, it is known as composite primary key. Primary key enforces the entity integrity of the table. Primary key always has unique data. A primary key length cannot be exceeded than 900 bytes. A primary key cannot have null value. There can be no duplicate value for a primary key. A table can contain only one primary key constraint. The main advantage of this uniqueness is that we get fast access.

54. SQL KEYS: Primary Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

55. SQL KEYS: Primary Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

56. SQL KEYS: Foreign Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables. In simple words you can say that, a foreign key in one table used to point primary key in another table.

57. SQL KEYS: Foreign Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Here are two tables first one is students table and second is orders table.

58. SQL KEYS: Foreign Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

59. SQL KEYS: Unique Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values. The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns. There is an automatically defined unique key constraint within a primary key constraint.

60. SQL KEYS: Unique Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

61. SQL KEYS: Composite Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness. Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key. Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

62. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL KEYS: Composite Key

63. SQL KEYS: Alternate Key School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Alternate key is a secondary key it can be simple to understand by an example: Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS. Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys. If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

64. SQL where clause School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B A  WHERE clause  in SQL is a data manipulation language statement. WHERE clauses are not mandatory clauses of SQL DML statements It returns only those queries which fulfill the specific conditions. WHERE clause is used in SELECT, UPDATE, DELETE statement etc. SELECT  column1,  column  2, ...  column  n   FROM      table_name    WHERE  [conditions]  

65. SQL AND School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B The SQL  AND  condition is used in SQL query to create two or more conditions to be met. It is used in SQL  SELECT, INSERT, UPDATE  and  DELETE SELECT columns FROM tables WHERE condition 1 AND condition 2; The SQL AND condition require that both conditions should be met. The SQL AND condition also can be used to join multiple tables in a SQL statement.

66. SQL AND School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Write a query to get the records from emp tables in which department of the employee is IT and location is Chennai. mysql >  SELECT  * FROM  emp  WHERE  Department =  "IT"   AND  Location =  "Chennai" ;  

67. SQL AND School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B mysql >  SELECT  * FROM  emp  WHERE  Department =  "IT"   AND  Location =  "Mumbai" ;   Write a query to update the records in emp tables in which department of the employee is Marketing, and the first name is Suraj. For that particular employee, set the updated value of the location as Delhi. mysql >  UPDATE  emp  SET  Location =  "Delhi"   WHERE  Department =  "Marketing"   AND First_Name  =  "Suraj" ;   Write a query to update the records in the emp table in which department of the employee is Finance and ID is 7. For that particular employee, set the updated value of the department as HR. mysql >  UPDATE  emp  SET  Department =  "HR"   WHERE  Department =  "Finance"   AND  ID = 7;   Write a query to delete the records from the emp table in which the last name of the employee is Jain, and the Location is Bangalore. DELETE   FROM  emp  WHERE   Last_Name  =  'Jain'   AND  Location =  'Bangalore' ;  

68. SQL OR School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B The SQL  OR  condition is used in SQL query to create a SQL statement where records are returned when any one condition met. It can be used in a  SELECT  statement,  INSERT  statement,  UPDATE  statement or  DELETE  statement.

69. SQL OR School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Write a query to get the records from emp tables in which department of the employee is Marketing or location is Noida. mysql >  SELECT  * FROM  emp  WHERE  Department =  "Marketing"   OR  Location =  "Noida" ;  

70. SQL WITH CLAUSE School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query. WITH  < alias_name >  AS  ( sql_sub-query_statement )   SELECT   column_list   FROM  < alias_name > [ table   name ]   [ WHERE  < join_condition >]  

71. SQL JOIN School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to combine two or more tables". The SQL JOIN clause takes records from two or more tables in a database and combines it together. ANSI standard SQL defines five types of JOIN : inner join, left outer join, right outer join, full outer join, and cross join.

72. Why sql join is used School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B If you want to access more than one table through a select statement. If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement. The joining of two or more tables is based on common field between them. SQL INNER JOIN also known as simple join is the most common type of join. How to use SQL join or SQL Inner Join?

73. Why sql join is used? School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

74. SELECT   Staff_ID ,  Staff_NAME ,  Staff_AGE , AMOUNT        FROM  STAFF s, PAYMENT p       WHERE  s.ID = p.STAFF_ID ;   School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B

75. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL JOIN In the SQL outer JOIN, all the content from both the tables is integrated together. Even though the records from both the tables are matched or not, the matching and non-matching records from both the tables will be considered an output of the outer join in SQL. There are three different types of outer join in SQL: Left Outer Join Right Outer Join Full Outer Join I nner join Cross Join

76. Loaded: 6.24%   SELECT   Staff_ID ,  Staff_NAME ,  Staff_AGE , AMOUNT    FROM  STAFF s, PAYMENT p   WHERE  s.ID = p.STAFF_ID ;  

77. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL OUTER JOIN In the SQL outer JOIN,  all the content from both the tables is integrated together. Even though the records from both the tables are matched or not, the matching and non-matching records from both the tables will be considered an output of the outer join in SQL. There are three different types of outer join in SQL: Left Outer Join Right Outer Join Full Outer Join Now let us take a deeper dive into the different types of outer join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

78. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL OUTER JOIN

79. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL OUTER JOIN Table 2: department

80. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL OUTER JOIN

81. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B SQL OUTER JOIN

82. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B LEFT OUTER JOIN If we use the left outer join to combine two different tables, then we will get all the records from the left table. But we will get only those records from the right table, which have the corresponding key in the left table. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName; Query 1 : Write a query to perform left outer join considering employee table as the left table and department table as the right table. mysql >  SELECT   e.EmployeeID ,  e.Employee_Name ,  e.Employee_Salary ,  d.DepartmentID ,  d.Department_Name   FROM  employee e  LEFT   OUTER   JOIN  department d  ON   e.EmployeeID  =  d.Employee_ID ;  

83. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B Query 2 : Write a query to perform left outer join considering loan table as the left table and borrower table as the right table. mysql >  SELECT   l.LoanID ,  l.Branch ,  l.Amount ,  b.CustID ,  b.CustName   FROM  Loan l  LEFT   OUTER JOIN  Borrower b  ON   l.LoanID  =  b.LoanID ;  

84. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B RIGHT OUTER JOIN Right outer join is the reverse of left outer join. If we use the right outer join to combine two different tables, then we will get all the records from the right table. But we will get only those records from the left table, which have the corresponding key in the right table. Syntax of writing a query to perform right outer join: SELECT  TableName1.columnName1, TableName2.columnName2  FROM  TableName1   RIGHT OUTER JOIN  TableName2   ON  TableName1.ColumnName = TableName2.ColumnName;   Write a query to perform right outer join considering employee table as the left table and department table as the right table. mysql >  SELECT   e.EmployeeID ,  e.Employee_Name ,  e.Employee_Salary ,  d.DepartmentID ,  d.Department_Name   FROM  employee e  RIGHT OUTER JOIN  department d  ON   e.EmployeeID  =  d.Employee_ID ;  

85. Write a query to perform right outer join considering loan table as the left table and borrower table as the right table. Query: mysql >  SELECT   l.LoanID ,  l.Branch ,  l.Amount ,  b.CustID ,  b.CustName   FROM  Loan l RIGHT OUTER JOIN Borrower b  ON   l.LoanID

86. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B FULL OUTER JOIN If we use a full outer join to combine two different tables,  then we will get all the records from both the table . we will get all the records from the left table as well as the right table. MySQL doesn't support FULL OUTER JOIN directly . So to implement full outer join in MySQL, we will execute two queries in a single query. The first query will be of LEFT OUTER JOIN, and the second query will be of RIGHT OUTER JOIN. We will combine the first and second query with the UNION operator to see the results of FULL OUTER JOIN. SELECT  TableName1.columnName1, TableName2.columnName2  FROM  TableName1   LEFT OUTER JOIN  TableName2   ON  TableName1.ColumnName = TableName2.ColumnName  UNION   SELECT  TableName1.columnName1, TableName2.columnName2  FROM  TableName1   RIGHT OUTER JOIN  TableName2   ON  TableName1.ColumnName = TableName2.ColumnName;   Write a query to perform full outer join considering the employee table as the left table and department table as the right table.

87. mysql >  SELECT   e.EmployeeID ,  e.Employee_Name ,  e.Employee_Salary ,  d.DepartmentID ,  d.Department_Name   FROM  department d  LEFT OUTER JOIN  employee e  ON   e.EmployeeID  =  d.Employee_ID   UNION   SELECT   e.EmployeeID ,  e.Employee_Name ,  e.Employee_Salary ,  d.DepartmentID ,  d.Department_Name   FROM  department d  RIGHT OUTER JOIN  employee e  ON   e.EmployeeID  =  d.Employee_ID ;  

88. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B LEFT JOIN If we use  left join to combine two different tables, then we will get all the records from the left table SELECT  TableName1.columnName1, TableName2.columnName2  FROM  TableName1  LEFT   JOIN  TableName2  ON  TableName1.ColumnName = TableName2.ColumnName;   Write a query to perform the left join operation considering the employee table as the left table and the department table as the right table. mysql >  SELECT   e.EmployeeID ,  e.Employee_Name ,  e.Employee_Salary ,  d.DepartmentID ,  d.Department_Name   FROM  employee e  LEFT   JOIN   department d  ON   e.EmployeeID  =  d.Employee_ID ; 

89. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B LEFT JOIN Write a query to perform the left join operation considering the loan table as the left table and the borrower table as the right table. mysql >  SELECT   l.LoanID ,  l.Branch ,  l.Amount ,  b.CustID ,  b.CustName   FROM  Loan l  LEFT   JOIN  Borrower b  ON   l.LoanID  =  b.LoanID ;  

90. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B RIGHT JOIN If we use the right join to combine two different tables, then we will get all the records from the right table . But we will get only those records from the left table, which have the corresponding key in the right table. Rest other records in the left table for which the common column value doesn't match with the common column value of the right table; displayed as NULL. Syntax: SELECT  TableName1.columnName1, TableName2.columnName2  FROM  TableName1  RIGHT   JOIN  TableName2  ON  TableName1.ColumnName = TableName2.ColumnName;     Write a query to perform a right join operation considering the employee table as the left table and the department table as the right table. mysql >  SELECT   e.EmployeeID ,  e.Employee_Name ,  e.Employee_Salary ,  d.DepartmentID ,  d.Department_Name   FROM  employee e  RIGHT   JOIN   department d  ON   e.EmployeeID  =  d.Employee_ID ; 

91. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B FULL JOIN The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found. What is SQL full outer join? SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don't care its matched or unmatched) from the both participating tables. Syntax for full outer join: SELECT  *   FROM  table1   FULL   OUTER   JOIN  table2   ON  table1.column_name = table2.column_name;  

92. School of Computing Science and Engineering C ourse Code : E2UC302B Course Name: DBMS Program Name: B.Tech Program Code: E2UC302B CROSS JOIN If we use the cross join to combine two different tables, then we will get the Cartesian product of the sets of rows from the joined table. When each row of the first table is combined with each row from the second table, it is known as Cartesian join or cross join. After performing the cross join operation, the total number of rows present in the final table will be equal to the product of the number of rows present in table 1 and the number of rows present in table 2. SELECT  TableName1.columnName1, TableName2.columnName2  FROM  TableName1  CROSS   JOIN  TableName2  ON  TableName1.ColumnName = TableName2.ColumnName;  

93. Write a query to perform the cross join operation considering the MatchScore table as the left table and the Departments table as the right table. Query: SELECT  *  FROM   MatchScore   CROSS   JOIN  Departments;