SQL
(Duration: 18hrs.)
01
Day 1
⦁ Introduction to DATA , DATABASE, RDBMS, SQL
⦁ What is SQL?
⦁ Why SQL?
⦁ SQL Structure
⦁ SQL Process
⦁ SQL Environment
⦁ Categories of SQL Statements
⦁ DDL
⦁ DML
⦁ DQL
⦁ DCL
⦁ CREATING & USING Database
⦁ Creating Table
⦁ Different ways to insert data into the table
⦁ Retrieving data from table
⦁ Major data types in SQL
⦁ Installation of SQL Server
⦁ Saving SQL Script
Assignment -1
⦁ Creation of Database
⦁ Creating Employee Table
⦁ Inserting the data into the table
⦁ Verifying the inserted data
02
Day 2
⦁ Retrieving selective data from table using clauses
⦁ WHERE
⦁ LIKE
⦁ TOP
⦁ Making changes in the existing data – update
⦁ Deleting data – delete
⦁ Changing the structure of table – Alter
⦁ Destroying tables & database – DROP
⦁ Deleting data using Truncate
⦁ Commit
⦁ Rollback
⦁ SQL Operators
⦁ Arithmetic Operators
⦁ Comparison Operators
⦁ Logical Operators
⦁ Other Operators (BETWEEN , IN, LIKE, IS NULL, DISTINCT, EXISTS)
Assignment – 2
⦁ Creating Salary Table
⦁ Inserting the data into the table
⦁ Verifying the inserted data
⦁ Retrieving selective data
⦁ Making the changes to the existing data
03
Day 3
⦁ Making the structure powerful – NORMALIZATION
⦁ Why Normalization?
⦁ Normalization Forms (1st , 2nd , 3rd …. n)
⦁ Implementing Normalization Using Constraints
⦁ PRIMARY Key
⦁ UNIQUE
⦁ NOT NULL
⦁ DEFAULT
⦁ CHECK
⦁ FOREIGN Key
Assignment – 3
⦁ Implement the above constraint on Employee & Salary Tables
04
Day – 4
⦁ More on Clauses
⦁ Between
⦁ In
⦁ Like
⦁ Count
⦁ Distinct
⦁ Order by
⦁ Group by
⦁ Having
⦁ Discussing frequently asked interview questions on above clauses
Assignment 4
⦁ In the EMP table display :
⦁ City wise count of employees arranged in descending order
⦁ Details of the employees who does not have an account on yahoo domain
⦁ From the emp_sal table display:
Designation wise total cost and number of members arranged in descending order of the total cost
⦁ Creating the below Inventory model
05
Day – 5
Working with multiple tables using JOINS
⦁ INNER JOIN
⦁ LEFT JOIN
⦁ RIGHT JOIN
⦁ FULL JOIN
⦁ CARTESIAN JOIN
⦁ SELF JOIN
⦁ Joining Queries using Set Clauses
⦁ UNION
⦁ UNION All
⦁ Intersect
⦁ Except
Assignment 5
In the EMP table display :
⦁ EID NAME CITY DOJ DEPT DESI SALARY of the DELHI employees
⦁ Details of all the employees whose salary details are not available.
⦁ In the inventory structure display :
⦁ PID, PDESC, CATEGORY, SNAME, SCITY
⦁ 2 ) DISPLAY OID , ODATE , CNAME, CADDRESS, CPHONE, PDESC, PRICE,OQTY, AMT
06
Day – 6
⦁ Enhancing the performance of Select Queries By Creating INDEXES
⦁ Single Filed Index
⦁ Composite Index
⦁ Drop Index
⦁ Implicit Index
⦁ Explicit Index
⦁ Clustered Index
⦁ Non Clustered Index
⦁ SQL Views – Another way of looking at the table
Why Views?
⦁ Creating Views
⦁ Data manipulation using views
⦁ Updating views
⦁ Dropping views
Assignment 6
⦁ Create a view EMP_SAL_DETAILS to get EID NAME DOJ DEPT DESI SALARY AS BASIC. Also calculate HRA (15% OF BASIC), PF (9% OF BASIC), NET (BASIC+HRA+PF), GROSS (NET-PF).
⦁ Create a view to display EID, NAME, DOJ, DESI, DEPT of all the managers joined in 2019.
⦁ Create a view to how many team members are there in each DEPARTMENT in each CITY, along with their TOTAL & AVERAGE SALARY.
In the inventory structure generate a view BILL. It should display
⦁OID,ODATE,CNAME,ADDRESS,PHONE,PDESC, PRICE, OQTY, AMOUNT
07
Day 7
⦁ SQL Functions – Examples on inbuilt functions in SQL
⦁ COUNT()
⦁ MAX()
⦁ MIN()
⦁ AVG()
⦁ SUM()
⦁ SQRT()
⦁ RAND()
⦁ RANK()
⦁ DENSE_RANK()
⦁ ROW_NUMBER()
⦁ CONCAT()
⦁ ASCII()
⦁ CHAR()
⦁ CHARINDEX()
⦁ LEFT()
⦁ RIGHT()
⦁ LEN()
⦁ LOWER()
⦁ UPPER()
⦁ SUBSTRING()
⦁ REPLACE()
⦁ REVERSE()
⦁ STUFF()
⦁ GETDATE()
⦁ DATEADD()
⦁ DATEDIFF()
⦁ DAY()
⦁ MONTH()
⦁ YEAR()
⦁ DATEPART()
⦁ CONVERT()
⦁ FORMAT()
ASSIGNMENT – 7
⦁ DEPARTMENT WISE TEAM SIZE AND AVERAGE SALARY OF ALL EMPLOYEES.
⦁ COUNT OF MANAGERS IN THE COMPANY.
⦁ MAXIMUM & MINIMUM SALARY OF AN ASSOCIATE.
⦁ DEPARTMENT WISE TEAM SIZE AND AVERAGE SALARY OF DELHI EMPLOYEES.
⦁ GENERATE OFFICIAL EMAIL OF THE EMPLOYEE TAKING 1ST CHARATCET OF FIRST NAME, 1ST CHARATCER OF LAST NAME, LAST 3 DIGITS OF EID, and FOLLED BY ‘RCG.COM’. EMAIL SHOULD BE IN A UPPER CASE.
⦁ NAME, CITY, PHNO & EMAIL OF THE EMPLOYEES WHOSE AGE >=40.
⦁ EID, NAME DOJ OF EMPLOYEES WHO HAVE COMPLETED 5 YEARS IN THE COMPANY
⦁ DETAILS OF THE MANAGERS HAVING BIRTHDAY IN THE CURRENT MONTH
⦁ EID, DEPT , DESI , SALARY OF THE EMPLOYEE WHO IS GETTING THE MAXIMUM SALARY
⦁ EID, NAME OF EMPLOYEE WHO HAS LONGEST NAME
⦁ Creating our own functions
⦁ Scalar Functions
⦁ Table Valued functions
Assignment 8
⦁ CREATE A FUNCTION CALC TO PERFORM THE SPECIFIED OPERATION ON THE GIVEN TWO NUMBERS.
⦁ FUNCTION TO GENERATE THE EMAIL ID BY ACCEPTING NAME & EID. EMAIL SHOULD CONTAIN 1ST CHARACTER OF 1ST NAME , 1ST CHARACTER OF LAST NAME, LAST 3 DIGITS OF EMP ID FOLLOWED BY @RCG.COM;
⦁ FUNCTION TO RETURN EID, NAME, DESI, DEPT, SALARY OF THE EMPLOYEES OF A SPECIFIED DEPARTMENT.
⦁ FUNCTION TO DISPLAY THE NAME, DEPT, DESI, CITY OF THE EMPLOYEES WHO HAVE THE BIRTHDAY IN THE CURRENT MONTH.
⦁ FUNCTION TO DISPLAY THE NAME, DEPT & DOJ OF EMPLOYEES WHO HAVE COMPLETED 5 YEARS IN THE COMPANY.
08
Day 8
Getting more from queries – using SUB QUERIES
⦁ Simple Sub query
⦁ Insert Sub query
⦁ Delete Sub Query
⦁ Update Sub Query
⦁ Correlated sub queries
⦁ Sub Query with exists
Assignment 9
⦁ EID, NAME, CITY OF GURGAON EMPLOYEES
EID, NAME , DOJ ,DEPT, DESI & SALARY OF ALL MANAGERS
REDUCE THE SALARY OF ALL DELHI EMPLOYEES BY 10%.
⦁ DISPLAY THE EID, NAME, CITY, DOJ, DEPT, DESI & SALARY OF THE TEAM MEMBERS OF DAVID & RAMESH GUPTA.
CREATE A TRAINING TABLE CONTAINING EID, NAME, DEPT. INSERT THE DETAILS OF OPS TEAM MEMBERS IN THE TRAINING TABLE.
DETAILS OF DIRECTORS SHOULD BE DELETED FROM THE TRAINING TABLE.
DISPLAY THE SALARY DETAILS OFF ALL EMPLOYES IF ANY OF THE TEAM MEMBER HAS SALARY MORE THAN 200000.
09
Day 9
Simplifying the process by creating STORED PROCEDURES
⦁ Simple procedure
⦁ Parameterized procedure
⦁ Insert procedure
⦁ Inserting data into multiple tables using procedure
⦁ Dropping Procedure
Assignment 10
⦁ CREATE BELOW PROCEDURES IN THE INVENTORY DATABASE AS SPECIFIED :
⦁ ADDSUPPLIER – SHOULD ADD THE SUPPLIER IN THE SUPLIER TABLE AND DISPLAY THE DETAILS OF THE NEW SUPPLIER ADDED.
⦁ ADDPRO – SHOULD ADD THE PRODUCT IN THE PRODUCT TABLE AND DISPLAY THE DETAILS OF THE NEW PRODUCT ADDED.
⦁ ADDCUST – SHOULD ADD THE CUSTOMER IN THE CUSTOMER TABLE AND DISPLAY THE DETAILS OF THE NEW CUSTOMER ADDED.
⦁ ADDORDER – SHOULD ADD THE ORDER IN THE ORDERS TABLE AND DISPLAY THE DETAILS OF THE ORDER. ORDER DATE SHOULD BE CURRENT DATE AND SHOULD COME AUTOMATICALLY.
10
Day 10
⦁ Transactions
⦁ Properties of Transactions
⦁ COMMIT Transaction
⦁ ROLLBACK Transaction
⦁ Create SAVEPOINT
⦁ Creating auto increment field
⦁ Creating sequence
⦁ Generating alpha numeric id using sequence
⦁ Creating a function to generate ids automatically
⦁ Collectively using function & procedure.
Assignment 11
⦁ CREATE A FUNCTION FOR AUTOGENERATION OF 5 CHARACTERS ALPHA NUMERIC ID. IT SHOULD ACCEPT 2 PARAMETERS A CHARACTER AND THE NUMBER AND RETURN THE ID BY CONCANATING THE CHARACTER, REQUIRED ZEROS AND THE SPECIFIED NUMBER.
⦁ RECREATE BELOW PROCEDURES IN THE INVENTORY DATABASE AS SPECIFIED (ALL THE ID s SHOULD BE AUTOMATICALLY GENERATED USING ABOVE CREATED FUNCTION AND SEQUENCES) :
⦁ ADDSUPPLIER – SHOULD ADD THE SUPPLIER IN THE SUPLIER TABLE AND DISPLAY THE DETAILS OF THE NEW SUPPLIER ADDED.
⦁ ADDPRO – SHOULD ADD THE PRODUCT IN THE PRODUCT TABLE AND DISPLAY THE DETAILS OF THE NEW PRODUCT ADDED.
⦁ ADDCUST – SHOULD ADD THE CUSTOMER IN THE CUSTOMER TABLE AND DISPLAY THE DETAILS OF THE NEW CUSTOMER ADDED.
⦁ ADDORDER – SHOULD ADD THE ORDER IN THE ORDERS TABLE AND DISPLAY THE DETAILS OF THE ORDER. ORDER DATE SHOULD BE CURRENT DATE AND SHOULD COME AUTOMATICALLY
11
Day 11
Implementing automation using triggers
⦁ Insert Trigger
⦁ Update trigger
⦁ Delete trigger
Inventory Management project
⦁ Enhancing the inventory model created in the previous assignment.
⦁ Real time management of inventory using triggers
⦁ Data insertion using procedures
⦁ Creating below reports:
⦁ Product report – details of the product along with the supplier details, total quantity sold till date and current stock
⦁ Customer Report – details of all the transactions done by the specified customer.
⦁ Daily report – details of all transaction done on the current date.
⦁ Supplier report – details of the products supplied by the specified supplier.
⦁ Automating purchase – whenever the stock quantity reaches below ROL the purchase order should automatically be placed in the purchase table as below