SQL OVERVIEW:
SQL:
SQL stands for Structured Query Language.
SQL is used to communicate with a database.
It is a standard language for accessing and manipulating databases.
DATABASE:
A database is an organized collection of structured information, or data, typically stored electronically in a computer system.
A database is usually controlled by a database management system (DBMS).
Databases are used for storing, maintaining, and accessing any sort of data.
Database available in market:
Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and MongoDB are all popular business database solutions.
TABLES:
Tables are database objects that contain all the data in a database.
In tables, data is logically organized in a row-and-column format similar to a spreadsheet.
****************************
Language ---> SQL
Database ---> SQL Server
Developer tool ---> Microsoft SQL Server Management Studio
****************************
Reasons: [Store data, Manipulate data, and Retrieve data]
SQL- (Structured Query Language): Used to communicate to perform various types of operations like storing data, manipulating data, and retrieving data.
Store data:
Data in tables is stored in row and column format at the logical level, but physically it stores data in something called data pages.
Manipulate data:
Data manipulation refers to the process of adjusting data to make it organized and easier to read.
Retrieve data:
You can use the SELECT statement to retrieve a specific row or retrieve data in a specific way.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[SQL TASK]- [Based on Microsoft SQL Server Database]-[Microsoft SQL Server Management Studio tool]
DAY-1
RESTRICTING AND SORTING DATA (part-1):
1. Write a query to display all the names that start with S.
2. Write a query to display all the names that start with S and end with the character n.
3. Write a query to display all the employees working for department 90 whose names start with S.
4. Write a query to display all the employees working in departments 10, 20, 50, and 90.
5. Write a query to display first name, salary, and department id of the employees not working for departments 10, 20, 50, and 90.
6. Write a query to display employees earning between 5000 and 7000.
7. Display first name, salary, department id, and manager id of employees without managers.
8. Display all records in the employees table and sort the first name in ascending order.
9. Display first name, department id, and salary from the employees table and sort the records (sort department id in ascending order and salary in descending order).
10. Create a query to display the last name and salary of employees earning more than $12,000.
11. Create a query to display the employee last name and department number for employee number 176.
12. Display the employee last name, job ID, and start date of employees hired between February 20, 2003, and May 1, 2005. Order the query in ascending order by start date.
13. Display the last name and department number of all employees in departments 20 and 50 in alphabetical order by name.
14. Display the last name and job title of all employees without managers.
15. Display the last name, salary, and commission for all employees earning commissions. Sort data in descending order of salary and commissions.
RESTRICTING AND SORTING DATA (part-2):
1. Select the employees in department 30.
2. List the first name, employee id, and departments of all clerks.
3. Find the department numbers and names of employees in all departments with department id greater than 20.
4. List name, job, and salary of all employees in department 20 who earn more than 2000/-.
5. Find all salesmen in department 30 whose salary is greater than 1500/-.
6. Find all employees whose designation is either manager or president.
7. Find all managers who are not in department 30.
8. Find all details of managers and clerks in department 10.
9. Find the names of employees earning between 1200/- and 1400/-.
10. Find employees who are clerks, analysts, or salesmen.
11. Find employees who are not clerks, analysts, or salesmen.
12. Find all employees whose total earning is greater than 2000/-.
13. Find all employees whose name begins or ends with M.
14. Find all employees whose names contain the letter M in any case.
15. Find employees who do not receive commission.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-2
AGGREGATE FUNCTIONS:
1. How many employees are currently listed in the Employees table?
2. What is the average salary of employees in the Employees table?
3. What are the highest and lowest salaries in the Employees table?
4. What is the average salary for each department in the Employees table?
5. Which departments have more than 5 employees in the Employees table, and how many employees are in each of those departments?
6. What is the average salary for each department, considering only employees with salaries greater than $50,000?
-- Using WHERE to filter rows before grouping
-- Using HAVING to filter groups after grouping
7. What is the total amount of salaries paid out in each department in the Employees table?
8. How many employees were hired each year according to the hire date in the Employees table?
9. How many employees were hired in each month of the year according to the hire date in the Employees table?
10. How many employees were hired on each day of the week according to the hire date in the Employees table?
11. Write a query to display the number of people with the same job.
12. Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively.
13. Display the minimum, maximum, sum, and average salary for each job type.
14. Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.
15. Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-3
ANALYTICAL FUNCTIONS VS AGGREGATE FUNCTIONS:
1. What is the average salary of all employees within our organization?
2. What is the average salary for employees in each department?
3. Displaying average salary against each employee record
4. Calculate total sum of salary department wise
5. Calculate cumulative sum of salary department wise
6. Calculate cumulative sum of the organization
7. Calculate Cumulative average of the salary department wise
8. Find the oldest joiner department wise using LAG Analytic function
9. Find the newest joiner department wise using LAG Analytic function
10. Find the oldest joiner department wise using LEAD Analytic function
11. Find the newest joiner department wise using LEAD Analytic function
12. How can I use SQL to rank employees' salaries within each department?
13. How can I use SQL to assign a dense rank to employees' salaries within each department?
14. Find employee with MAX salary department wise
15. Find employee with MIN salary department wise
DAY-4
DATE FUNCTIONS:
Basic Date Functions
1. How do you get the current date and time in SQL Server?
2. How can you extract the year, month, and day from a date in SQL Server?
3. How do you find the difference between two dates in days?
Date Formatting and Manipulation
4. How can you format a date in SQL Server?
5. How do you add or subtract days from a date in SQL Server?
-- Add 10 days
-- Subtract 10 days
6. How can you get the first day of the current month?
Date Parts and Rounding
7. How do you get the last day of the current month?
8. How can you extract just the date part from a DATETIME value?
9. How do you find the start of the week for a given date?
Current date
Advanced Date Functions
10. How do you find the week number of the year for a given date?
11. How can you find the date that is exactly one year before a given date?
Date and Time Arithmetic
12. How can you get the number of weeks between two dates?
2024-01-01', '2024-12-31'
13. How do you calculate the number of hours, minutes, and seconds between two DATETIME values?
'2024-01-01 08:00:00', '2024-01-02 12:30:00'
14. How can you extract the quarter of the year from a date?
15. How can you get the last day of the previous month?
DAY-5
STRING FUNCTIONS:
Basic String Manipulation
1. How can you extract the first name from a full name column if the full name is stored in a single column (e.g., Full Name) in the format "First Name Last Name"?
2. How would you convert all employee names in the Full Name column to uppercase?
3. What SQL query would you use to find employees whose first names start with the letter 'J'?
4. How can you concatenate the First Name and Last Name columns to display the Full name of employees?
5. How can you format the Phone Number column to include dashes (e.g., from "1234567890" to "123-456-7890")?
6. How would you find all employees whose email addresses contain the domain "example.com"?
7. Write a query to find employees whose last names have exactly 5 characters.
8. How can you extract the domain part of an email address (e.g., from "user@example.com" to "example.com")?
9. Write a query to replace all occurrences of a certain character (e.g., '.' with '-') in the Phone Number column.
10. How would you find employees with names that include the substring "Admin" anywhere in the Full Name column?
11. Write a query to return the last 4 characters of the Phone Number column for each employee.
12. How can you reverse the characters in the Last Name column for all employees?
13. How can you replace all instances of "Manager" with "Supervisor" in the Job Title column?
14. How can you find employees whose Email Address column contains a specific pattern such as ".org"?
15. Write a query to extract the username part of an email address (i.e., the part before the '@') from the Email Address column.
DAY-6
CASE AND CONVERT FUNCTIONS:
CASE Function Questions
1. Write a query to display each employee's last name and indicate whether they receive a commission. The output should show "No Commission" if the Commission percentage is NULL, and "YES Commission" if there is a commission percentage.
2. How can you determine if a given date (current date) is a weekend or a weekday?
3. How would you categorize employees into different experience levels based on their years with the company? For example, "Junior" for less than 2 years, "Mid-Level" for 2 to 5 years, and "Senior" for more than 5 years.
4. How can you display a message indicating whether each employee's salary is above or below the average salary? Label the column as 'Salary Status'.
5. How would you use the CASE function to show different messages based on employee job titles? For instance, "Management" for titles containing "Manager", "Technical" for titles containing "Engineer", and "Other" for all other titles.
6. Write a query to classify employees into different salary bands: "Low" for salaries below $40,000, "Medium" for salaries between $40,000 and $80,000, and "High" for salaries above $80,000.
CONVERT Function Questions
7. How can you convert the hire date column to a varchar format of 'YYYY-MM-DD'?
8. Write a query to convert the Salary column from numeric to a currency format with two decimal places.
9. How can you convert the hire date column to a specific format, such as "Month Day, Year" (e.g., "August 22, 2024")?
Combining CASE and CONVERT Functions
10. Write a query that uses CASE to classify employees based on the length of their Phone Number, and then converts the length into a string for reporting.
DAY-7
DDL-DATA DEFINITION LANGUAGE:
Creating and Modifying Tables:
1. How would you create a table named Employees with the following columns: Employee Id (integer, primary key), First Name (varchar (50)), Last Name (varchar (50)), Hire Date (date), and Salary (decimal (10, 2))?
2. How can you modify an existing table Employees to add a new column Email Address of type varchar (100)?
3. How would you change the Salary column in the Employees table to allow NULL values?
4. Write a query to rename the Last Name column to Surname in the Employees table.
Dropping and Renaming Tables
5. How would you delete the Employees table from the database?
6. What is the SQL command to rename the table Employees to Staff?
Constraints and Indexes
7. How can you add a unique constraint to the Email Address column in the Employees table to ensure all email addresses are unique?
8. Write a query to add a foreign key constraint to the Employees table that references the Department Id column in the Departments table.
9. Write a query to list all tables in the current database.
10. How can you create a table named Projects with an ID column as a primary key, a Project Name column of type VARCHAR(100), a Start Date column of type DATE, and a Budget column of type DECIMAL(15, 2)?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-8
DML-DATA MANIPULATION LANGUAGE AND TCL-TRANSACTION CONTROL LANGUAGE:
Data Manipulations:
1. How would you insert a new record into the Employees table with Employee Id 101, First Name 'John', Last Name 'Doe', Hire Date '2023-05-15', and Salary 75000?
2. Write a query to update the Salary of the employee with Employee Id 101 to 80000.
3. How would you delete the record of the employee with Employee Id 101 from the Employees table?
4. Assuming the Employees table has a Department Id column, how would you update the Department Id to 5 for all employees whose Salary is greater than 70000?
5. Write a query to insert multiple records into the Projects table with Project Name 'Project X', 'Project Y', and 'Project Z' and their respective Start Date and Budget. Use appropriate values for ID if the column is auto-incremented.
6. Write a query to select all employees from the Employees table and replace any NULL values in the Email Address column with 'No Email'.
7. How would you update all NULL values in the Commission PCT column to 0 in the Employees table?
8. If you have a Projects table with columns Project Name and Start Date, how would you insert multiple rows into this table using a single query?
9. How would you delete all records from the Projects table where the Start Date is before '2024-03-01'?
10. Suppose you want to update the Email Address of all employees in the Employees table to 'updated@example.com' where the Last Name is 'Doe'. How would you perform this update?
Data Transactions:
11. What is a transaction in SQL Server, and why is it important?
12. What is the purpose of the COMMIT statement in SQL Server?
13. What does the ROLLBACK statement do in SQL Server?
14. What is a save point, and how can you use it in SQL Server?
15. What are the differences between COMMIT, ROLLBACK, and SAVE TRANSACTION in SQL Server?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-9
CONSTRAINTS:
Primary Key Constraints
1. Create a table named Students with the following columns: Student ID (int), First Name (varchar (50)), Last Name (varchar (50)), and Date of Birth (date). Ensure that Student ID is the primary key.
2. How would you insert a new student into the Students table with Student ID 1001, First Name 'Alice', Last Name 'Smith', and Date Of Birth '2000-01-01'?
Unique Constraints
3. Create a table named Employees with Employee ID (int), Email (varchar (100)), First Name (varchar (50)), and Last Name (varchar (50)). Ensure that Email is unique.
Foreign Key Constraints
4. Create a table named Departments with Department ID (int) and Department Name (varchar (100)). Then, create another table named Employees with Employee ID (int), First Name (varchar (50)), Last Name (varchar (50)), and Department ID (int) that references Department ID in the Departments table.
Check Constraints
5. Create a table named Products with Product ID (int), Product Name (varchar (100)), Quantity (int), and Price (decimal(10, 2)). Add a check constraint to ensure that Quantity is greater than or equal to 0 and Price is greater than 0.
6. How can you modify an existing table to add a check constraint that ensures the Salary column in the Employees table is between 30,000 and 150,000?
Default Constraints
7. Create a table named Orders with Order ID (int), Order Date (date), and Status (varchar (50)). Set a default value of 'Pending' for the Status column.
8. How would you add a default constraint to an existing column Order Date in the Orders table to set it to the current date if no value is provided?
Composite Constraints
9. Create a table named Course Enrollments with Student ID (int), Course ID (int), and Enrollment Date (date). Set a composite primary key on Student ID and Course ID.
Unique Constraint
10. How would you create a unique constraint on a combination of First Name and Last Name in the Employees table to ensure that no two employees can have the same combination of first and last names?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-10
JOINS:
PART-01
1. Inner Join
Retrieve a list of all employees and their corresponding department names. You need to join the Employees table with the Departments table.
Employees->First Name, Last Name
Departments->Department Name
2. Left Join
Get a list of all employees and their job titles, including employees who do not have a job title assigned. Join the Employees table with the Jobs table.
Employees->First Name, Last Name
Jobs->Job Title
3. Right Join
List all departments and the employees working in them, including departments with no employees. Join the Departments table with the Employees table.
Employees->First Name, Last Name
Departments->Department Name
4. Full Outer Join
Find all employees and all departments, showing employees even if they are not assigned to any department, and showing departments even if no employees are assigned to them. Join the Employees table with the Departments table.
Employees->First Name, Last Name
Departments->Department Name
5. Self-Join
Display employee name and their respective manager name.
Employees->Employee id, Employee Name, Manager Name
6. Cross Join
Employees->First Name, Last Name
Departments->Department Name
Generate a list of all possible combinations of employees and departments, regardless of their actual relationships. Use a cross join between the Employees table and the Departments table.
7. Join with Aggregation
Retrieve the total number of employees in each department. Join the Employees table with the Departments table, and group the results by department.
Employees->Number of Employees
Departments->Department Name
8. Join with Subqueries
Find the names of employees who earn more than the average salary in their department. Assume you have the following tables:
Employees->First Name, Last Name
9. Join with Multiple Tables (inner join)
A.) List the names of employees, their job titles, and their department names. You need to join the Employees, Jobs, and Departments tables.
Employees->First Name, Last Name
Jobs->Job Title
Departments->Department Name
B.) List employee names, job titles, department names, city names, and country names. You must join tables of employees, jobs, departments, locations, and countries.
Employees->First Name, Last Name
Jobs->Job Title
Departments->Department Name
Locations->City
Countries->Country Name
10. Advanced Join with Filtering
Retrieve a list of employees along with their department names```html
but only for those departments that have more than 5 employees. Use a subquery in conjunction with the join.
----------------------------------
PART-02
1. Write a query to display the last name, department number, and department name for all employees.
2. Create a unique listing of all jobs that are in department 80. Include the location of the department in the output.
3. Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission.
4. Display the employee last name and department name for all employees who have an a (lowercase) in their last names. Place your SQL statement in a text file named test4.sql.
5. Write a query to display the last name, job, department number, and department name for all employees who work in Toronto.
6. Display the employee last name and employee number along with their manager last name and manager number. Label the columns Employee, Employee#, Manager, Manager#, respectively. Place your SQL statement in a text file named test6.sql.
7. Modify test6.sql to display all employees including King, who has no manager. Place your SQL statement in a text file named test7.sql. Run the query in lab4_7.sql
8. Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.
9. Create a query that displays the name, job, department name, salary for all employees.
10. Display the names and hire dates for all employees who were hired before their managers, along with their manager names and hire dates. Label the columns Employee name, Employee Hired, Manager, and Manager Hired, respectively.
DAY-11
SET OPERATORS:
Combine Aggregate Results Using UNION
1. Write a query that uses UNION to combine the average salary and the maximum salary of employees in each department.
Find Common Average Salaries Using INTERSECT
2. Write a query to find departments where the average salary is the same in two different years. Assume you have two tables: Employees_2022 and Employees_2023, both with Department ID and Salary columns.
Find Unique Aggregate Results Using EXCEPT
3. Write a query to find departments where the total salary of employees in 2023 is greater than that in 2022, using EXCEPT. Assume you have two tables: Employees_2022 and Employees_2023.
Combine Results with UNION ALL and Aggregate Functions
4. Write a query using UNION ALL to combine the total number of employees and the average salary in each department. Sort the results by the total number of employees in descending order.
Find Employees with Unique Aggregated Data Using INTERSECT
5. Write a query to find the departments where both the average salary and the maximum salary are the same in two different tables: Employees A and Employees B.
Aggregate and Compare Results with EXCEPT
6. Write a query to find departments where the total salary in the Employees table is greater than in the Retired Employees table.
Union All and Filter Aggregated Results
7. Write a query to get the combined total salary and employee count for each department from Employees_2023 and Employees_2024 tables, but only include departments that are present in both years.
Combine Results from Multiple Years Using UNION ALL
8. Write a query to combine the average salary and total salary for each department across the years 2022 and 2023. Use UNION ALL to combine these results.
List all employees who either joined first or most recently.
9. To get a combined view of employees who joined either the earliest or the most recently, you can use set operators such as UNION.
Combine Results Using Set Operators
10. Combine the results of employees with the highest and lowest salaries using UNION to get a unified list of these employees.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-12
SUBQUERIES:
1. Write a query to display the last name and hire date of any employee in the same department as zlot key. Exclude zlot key.
2. Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary.
3. Display the last name, department number, and job ID of all employees whose department location ID is 1700.
4. Display the last name and salary of every employee who reports to King.
5. Display the department number, last name, and job ID for every employee in the Executive department.
6. Find Employees with Higher Salaries than the Average
7. Find the last names and salaries of employees who work in the same department as 'John Doe'.
8. Departments with More Employees than the Average
9. Employees Who Report to Managers with a Salary above a Certain Amount
10. Find Employees with No Subordinates
11. Top-N Employees by Salary in Each Department
12. Employees Earning More than Their Manager
13. Find Employees with the Highest Salary in Their Department
14. Departments with No Employees
15. Employees Who Share a Job Title with Any Employee in a Specific Department
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-13
VIEWS:
Create a View to Display Employee Names and Salaries
1. Create a view called Employee Salaries that displays the first name, last name, and salary of all employees.
Create a View with Filtered Data
2. Create a view called High Earners that only includes employees with a salary greater than $15,000.
Create a View with Joins
3. Create a view named Employee Department that displays employee names along with their department names by joining the Employees and Departments tables.
Update a View
4. Assuming you have a view Employee Salaries, update the salary of employees in this view by increasing it by 10%.
Drop a View
5. Drop the view High Earners that you created previously.
View to Display Managers and Their Direct Reports
6. Create a view named [Employees Reporting to Manager’s With Job Title] that lists employees and their manager’s job titles where the manager's job title is 'Manager'.
Create a View to Display Department Average Salaries
7. Create a view named [Department Average Salaries] that shows the average salary for each department.
View to Aggregate Data by Year
8. Create a view named [Annual Employee Salaries] that displays the total salary paid to employees each year. Assume you have a Hire Date column in the Employees table.
View for Salary Ranges
9. Create a view named [Salary Ranges] that shows the salary ranges (Min Salary and Max Salary) for each department.
View to Display Managers and Their Direct Reports
10. Create a view named [Manager Employee Details] that lists managers (by their employee ID and name) and their direct reports employee IDs and names.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DAY-14
STORED PROCEDURES:
1. What is a Stored Procedure in SQL Server?
2. How do you create a stored procedure in SQL Server?
You can create a stored procedure using the CREATE PROCEDURE statement.
3. How do you execute a stored procedure?
You can execute a stored procedure using the EXEC or EXECUTE statement.
4. What are parameters in a stored procedure, and how are they used?
5. How do you alter an existing stored procedure?
You can alter an existing stored procedure using the ALTER PROCEDURE
6. What are the benefits of using stored procedures?
7. How do you pass parameters to a stored procedure?
Create a Simple Stored Procedure
8. Create a stored procedure named [Get Employee Details] that retrieves the first name, last name, and salary of an employee given their Employee ID.
Stored Procedure with Output Parameter
9. Create a stored procedure named [Get Department Employee Count] that returns the number of employees in a department. Use an output parameter for the count.
Stored Procedure to Delete Records
10. Create a stored procedure named [Delete Employees By Department] that deletes all employees from a specified department.
DAY-15
INDEX:
1. What is an index in SQL Server, and why is it used?
2. What are the different types of indexes available in SQL Server?
3. How do you create an index on a table?
4. What is the difference between a clustered and a non-clustered index?
5. How can you view the existing indexes on a table?
Create a Clustered Index
6. Create a clustered index on the Employee ID column of the Employees table.
Create a Non-Clustered Index
7. Create a non-clustered index on the Last Name column of the Employees table.
Drop an Index
8. Drop the non-clustered index named [IX Employee Last Name] from the Employees table.
Create a Unique Index
9. Create a unique index on the Email column of the Employees table to ensure that all email addresses are unique.
Create an Index on Multiple Columns
10. Create a non-clustered index on the Department ID and Salary columns of the Employees table.
-----------------------------------------------------------------------
