SQL Concepts and Queries PDF

M.Sc. IT Data Science and Analytics (5 Years Integrated) Subject: Data Management And Relational Data Modelling Unit-4 SQL Concepts and Queries  Objective: 1. SQL – Introduction, Syntax and Data Type – What are DDL, DML, DCL, TCL...

M.Sc. IT Data Science and Analytics (5 Years Integrated) Subject: Data Management And Relational Data Modelling Unit-4 SQL Concepts and Queries  Objective: 1. SQL – Introduction, Syntax and Data Type – What are DDL, DML, DCL, TCL, DQL Commands ? 2. SQL – Clauses – Having, Where, Order By, Group By 3. SQL – Joins & Sub Queries 4. SQL - Operators, Expressions and Comments 5. SQL – Views 6. SQL – Store Procedure, Cursor 7. SQL – Functions  Contents 1. SQL – Introduction, Syntax and Data Type. What are DDL, DML, DCL, TCL, DQL Commands ? 2. SQL – Clauses – Having, Where, Order By, Group By 3. SQL – Joins & Sub Queries 4. SQL - Operators, Expressions and Comments 5. SQL – Transactions, Indexes and Views 6. SQL – Constraints and Functions 7. SQL – Store Procedure, Cursor, Trigger 11/29/2024 3 1 – All Final Practical's – SQL Introduction Syntax and Data Type  SQL – Structured Query Language  Used To retrieve data from database 1.1 – SQL Statements - Most of the actions you need to perform on a database are done with SQL statements. SQL statements consists of keywords that are easy to understand. The following SQL statement returns all records from a table named "Customers": 1.2 – Tables In SQL - A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"), and contain records (rows) with data. In this tutorial we will use the well-known North wind sample database (included in MS Access and MS SQL Server). 1.3 – SQL Data Types – SQL supports almost all data types. Popular Data Types include int, char, varchar, number, date, Boolean. 11/29/2024 4 11/29/2024 5 1.4 – What SQL Can Do ?? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views 11/29/2024 6 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 11/29/2024 7 1.5 - Data Types In SQL – String Data Types 11/29/2024 8 1.5.2 - Data Types In SQL – Numeric Data Types 11/29/2024 9  Out Of the Above mainly used data types are char, varchar, int, number, date, datetime, nvarchar,varchar2,varbinary,text, nchar for working with SQL Queries on projects and tasks. 11/29/2024 10 What are DDL, DML, DCL, TCL, DQL Commands ⮚ DBMS Commands consists of 5 Languages :: 2.1 – Data Definition Language - DDL defines structure of any database. Auto-commit commands data once changed cant be modified or restored later on even through roll back. Basic commands include ⮚ Create ⮚ Alter ⮚ Drop ⮚ Truncate. ⮚ DDL - Create - Create command is used to create any logical table which is later saved into physical file on disk. Data Definition Language - DDL - Alter ⮚ It is used to alter the structure of the database. ⮚ This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute. ⮚We can delete column using this command as per given below output Data Definition Language - DDL - Truncate ⮚ It is used to delete all the rows from the table and free the space containing the table. ⮚ TRUNCATE TABLE Categories; ⮚ Deletes Data inside the table categories but not the table itself. Data Definition Language - DDL - Drop ⮚ Drop Command is specifically used to delete entire table when function of that table is completed so table is permanently deleted safely. ⮚ Syntax - drop table branch – deletes table from the database and can’t be restored;  Data Manipulation Language - DML as the word indicates is used to manipulate the data in database. ⮚ Basic commands include ⮚ Insert ⮚ Update ⮚ Delete ⮚ Insert - Used to insert or add any data into the database INSERT INTO EMPLOYEE(empId,name,dept) VALUES (1, 'Clark', 'Sales');INSERT INTO EMPLOYEE(empId,name,dept) VALUES (2, 'Dave', 'Accounting');INSERT INTO EMPLOYEE(empId,name,dept) VALUES (3, 'Ava', 'Sales'); Data Manipulation Language - DML - Update- ⮚ Used to modify or change any data into database. ⮚ It is executed through a specific statement “where” which specifies that updating is done by taking primary key column into consideration. ⮚ Basic syntax as under Data Manipulation Language - DML - Delete - ⮚ Used to remove one or more row from a table. ⮚ Basic syntax as under. ⮚ Only deletes row from the table but not the entire table ⮚ It is not an auto commit command so we can get data back via rollback command. Data Control Language - DCL - Grant – Used to give access to any user. For e.g read, write. Data Control Language - DCL - Revoke – Used to used to revoke the permissions/access that was granted via the GRANT command. It is mostly used to revert back to the time when no access was specified, i.e., withdrawing the permission that was authorized to carry out specific tasks. ⮚ REVOKE ON FROM ⮚ REVOKE ALL PRIVILEGES ON product stock FROM PQR;  Transaction Control Language - TCL - TCL commands are used to used to manage transactions in the database. ⮚ These are used to manage the changes made by DML statements. ⮚ Basic TCL commands: Commit, Rollback, and Save point. ⮚ The COMMIT command is used to permanently save any transaction into the database. INSERT INTO Employee VALUES(113, 'Rose'); COMMIT; ⮚ The ROLLBACK command basically restores the database to the last committed state.(ROLLBACK TO A; SELECT * FROM Employee;) ⮚ The SAVEPOINT command is used to temporarily save a transaction so that we can roll back to the saved point whenever necessary. ⮚ UPDATE Employee SET name = 'Oliver' WHERE id = '110'; SAVEPOINT A; INSERT INTO Employee VALUES(114, 'Prince'); SAVEPOINT B; INSERT INTO Employee VALUES(115, 'Smith'); SAVEPOINT C;  Data Query Language - DQL – DQL command is used to query any data from the table into the database. ⮚ DQL contains only one command that is select to fetch data basic syntax as under:: ⮚ select * from tab – fetches all tables from oracle on to the console.  Data Query Language - DQL – DQL – Select Command :: ⮚ Select * from student fetches all rows in a tabular format so that we can later on check the changes or make the changes if any in short it is used to display our records in the database. 2.0 – SQL Clauses – Where, Having, Order By, Group By Where Clause is used to specify a specific condition. E.g. SELECT EMPNAME, EMPSAL, EMPJDATE from employee where empcity = ‘ahmedabad’; E.g. SELECT EMPNAME, EMPCITY, EMPSTATE, EMPBDATE where empsal > 50000; Update emp set empsal = 50000 where empjdate = ‘01-jan-19’; 11/29/2024 23 2.1 – SQL Clauses – Having Clause 11/29/2024 24 2.2 – SQL Clauses – GROUP BY & ORDER BY 11/29/2024 25 3.0 – SQL Joins – Cross Join Or Cartesian Product 3.1 - SQL JOINS – Fetching Specific Record Of Data Through Cross Join 3.2 - SQL JOINS – Inner Join 3.3 - Fetching Specific Record Through Inner Join 3.4 - Fetching Specific Record Through Left Join 3.5 - Fetching Specific Record Through Right Join 3.6 - Fetching specific record through full outer join 3.7 - Fetching specific record through self join 3.8 – Sub Queries In SQL 3.8.1 - SQL – SUB QUERY PRACTICAL 4.0 - SQL – Operators, Expressions & Comments – ‘=‘ Operator 11/29/2024 36 4.1 - SQL – Operators, Expressions & Comments – Less Than ‘’Operator 11/29/2024 38 4.3 - SQL Operators – Greater Than Equal To‘>=’ 4.4 - SQL Operators – Greater Than Equal To‘