SQL Exercises

SQL Day 1

SOOPRO Pathshala Exercises

Day 1

1. Create database name mydb.

2. Write a query to create table having id,name,gender,city and salary columns, under mydb database.

3. Rename database mydb to mydatabase.

4. Write a query to create table employee having id,name,gender,city and salary columns, under mydatabase database.

5. Write a query to select particular column of table.

6. Write a query to view all records of table.

7. Write a query to add new column of table.

8. Write a query to create table of employee having id,name,gender,city and salary.

9. Write a query to print details of employee whose first name starts with 'a'.

10. Write a query to print details of employee whose city starts with 'n'.

11. Write a query to print details of employee whose first name ends with 'r'.

12. Write a query to display maximum salary of employee

13. Write a query to display minimum salary of employee

14. Write a query to display total salary of employee

15. Write a query to display average of salary of employee

16. Write a query to add new column in employee table

17. Write a query to update multiple fields of employee table

18. Write a query to drop a column from employee table

SQL Day 2

SOOPRO Pathshala Exercises

Day 2

1. Create a student table having id,name,gender,city,mobile and roll number.Insert 5 rows in student table.

2. Write a query to display unique student name using distinct function.

3. Write a query to add new column address in student table.

4. Write a query to change data type of roll number column to bigint.

5. Write a query to display particular records of student table.

6. Write a query to drop address column.

7. Write a query to update multiple fields of student table.

8. Write a query to truncate student table.

9. Write a query to drop student table.

10. Write a query to drop current database.

SQL Day 3

SOOPRO Pathshala Exercises

Day 3

1. Write a query to print unique city of all employee

2. Write a query to print sum of total salary of all employee.

3. Write a query to print total rows of employee table.

4. Write a query to count total employees of employee table.

5. Write a query to print average of employee salary.

6. Write a query to print record of all employees whose date of birth between 1991 to 2005

7. Write a query to print record of employees whose city in either noida or kanpur.

8. Write a query to print record of employees whose city in kanpur,delhi and noida

9. Write a query to print record of employees whose first name start with 'm'.

10. Write a query to print top 3 employees.

11. Write a query to print record of employee and their name in ascending order.

SQL Day 4

SOOPRO Pathshala Exercises

Day 4

1. Write a query to print average of salary of employee whose name starts with 'm'

2. Write a query to print average of salary of employee whose name ends with 'n'

3. Write a query to print sum of salary of employee whose city in noida,delhi and punjab

4. Write a query to print sum of salary of employee whose city not in faridabad,punjab and delhi

5. Write a query to apply primary key on table column

6. Write a query to apply unique key on table column

7. Write a query to do not allow null value on specific column

8. Write a query to allow number in column if it is positive number

9. Write a SQL statement that display all the information about all salesperson having salesman_id,name,city and commission.

10. Write a SQL query to display sum of max and min of employee salary

11. Write a SQL query to display average of max and min of employee salary

12. Write a sql query to display cities of employees whose salary is greater than 20000

13. Write a sql query to display gender of employee whose salary is less than 50000

SQL Day 5

SOOPRO Pathshala Exercises

Day 5

1. Create two tables department and employee, in department table add four records having id-1,2,4 & 5 and in employee table add six records having id-1,2,3,4,5 and 6.

2. Perform inner join on department and employee tables.

3. Perform left join on department and employee tables.

4. Perform right join on department and employee tables.

5. Perform full outer join on department and employee tables.

6. Write a sql statement that displays all the information about all salesperson.

7. Write a sql query to display the sum of two number 10 and 15 from RDBMS server.

8. Write a sql query to display the result of an arithmetic operation.

9. Write a sql statement to display the specific column such as name and commission for all the salesperson.

10. Write a sql query to display the column in a specific order, such as order date, and purchase amount for all the orders.

SQL Day 6

SOOPRO Pathshala Exercises

Day 6

1. Write a query to print even and odd using scalar function in sql.

2. Write a query to print positive or negative number using scalar function in sql.

3. Write a query to print square of any number using scalar function in sql.

4. Write a query to print cube of any number using scalar function in sql.

5. Write a query to print sum of three integer using scalar function in sql.

6. Write a query to print sum of three integer using scalar function in sql.

7. Write a query to print your name using scalar function in sql.

8. Write a query to print total employees gender wise. HINT: use stored procedure having (input and output parameter).

9. Write a query to print total student city wise. HINT: use stored procedure having(input and output parameter)

10. Write a query to perform inner join inside stored procedure and print it.

SQL Day 7

SOOPRO Pathshala Exercises

Day 7

1. Write a query to return total sum of employee salary using scalar function.

2. Write a query to return average salary of employee using scalar function.

3. Write a query to return records of employee whose name start with 'a' using inline table valued function.

4. Write a query to return records of employee whose salary greater than 10000 using inline table valued function.

5. Write a query to perform union and union all in sql.

6. Write a query to perform intersect and except in sql.

7. Write a query to print total salary of employee city wise.

8. Write a query to print average salary of employee gender wise.

9. Write a query to print average salary of employee city wise.

SQL Day 8

SOOPRO Pathshala Exercises

Day 8

1. Find 6th hignest salary of employee.

2. Find 3rd highest salary of employee.

3. Create procedure that print any highest salary.

4. Create procedure that print sum of second highest salary and fourth highest salary.

5. Create procedure to return difference of sum of salary and max of salary using output parameter.

6. Print inner join using stored procedure on window form.

7. Print difference of second highest and fourth highest salary of employee using stored procedure.

8. Create trigger on employee table that gets fired and print some message on insertion of any record in employee table

9. Create trigger on student table that gets fired and print message on deletion of any record from employee table.

10. Create trigger on employee table that print deleted record from employee table.

SQL Day 9

SOOPRO Pathshala Exercises

Day 9

1. Create clustered index on customer(id) table.

2. Create non clustered index on person(id) and person(name) table

3. Create unique index on employee(id) table.

4. Write a query to drop index

5. Write a query to view all indexes of current table.

6. Use rollback transaction to delete record of student.

7. Use commit transaction to delete record of student.

8. Try to see uncommited transaction in other connection.

9. Use try and catch block for divide by zero exception in sql.

10. Execute transaction for update query under stored procedure.

SQL Day 10

SOOPRO Pathshala Exercises

Day 10

1. Write a query to return table using table valued function..

2. Waq to return average of employee salary using inline table valued function.

3. Waq to return product of employee salary using inline table valued function.

4. Write short notes on function in sql.

5. Write short notes on trigger in sql.

6. Do practice on aggregate function.

7. Create a function that takes a number as an argument, increments the number by +1 and returns the result.

8. Create a function that takes age in years and returns the age in days.

9. Create a function that takes voltage and current and returns the calculated power.

10. Create a local temporary table name person having id,name,gender, city and salary.

SQL Day 11

SOOPRO Pathshala Exercises

Day 11

1. Create a local temporary table name student having id,name,gender, city and salary.

2. Create a global temporary table name customer having id,name,amout, city

3. Create a local temporary table name student having id,name,gender, city and salary.

4. Create a trigger inside procedure for insert operation on sql table . When trigger gets fired message should be displayed.

5. Create a trigger inside procedure for delete opertaion on sql table. when trigger gets fired message should be displayed.

6. Create cursor to fetch first record of student table without variable.

7. Create cursor to fetch last record of student table without variable.

8. Create cursor to fetch second last record od student table without variable.

9. Create cursor to fetch id and name of first record using variable.

10. Return total salary of employees of a table inside procedure.

11. Return total salary of employees citywise inside procedure.

SQL Day 12

SOOPRO Pathshala Exercises

Day 12

1. Waq to add new column in sql table

2. Waq to change data type of column in sql table

3. Waq to change size of data type of column in sql table.

4. Waq to drop column of sql table.

5. Waq to rename column name of sql table.

SQL Day 13

SOOPRO Pathshala Exercises

Day 13

1. Waq to add column in specified position (hint- do r&d)

2. Waq to drop last column of employee table.

3. Waq to drop first column of employee table.

4. Waq to copy only top 3 records from person table to person1 table.

5. Waq to copy only top 5 records from person table to person1 table.

SQL Day 14

SOOPRO Pathshala Exercises

Day 14

1. Waq to count toal rows of employee table

2. Waq to count only distinct rows of employee table

3. Waq to find duplicate value in column of sql table.

SQL Day 15

SOOPRO Pathshala Exercises

Day 15

1. Waq to create view for top 3 records of employee table

2. Waq to create view for last 3 records of employee table.

3. Waq to print 2nd highest and 4th highest salary of employee.