1) consider a table called Students which contains student_id, first_name, last_name,
department, and age as Columns.create a simple select stored procedure that will select and
display student records based on a specified department.
2) Create a database, switch to that database, create table ‘student’s details’. Create a
procedure called ‘Show’ to view all the content of the table. Rename the procedure name
from ‘Show’ to ‘Display’.
3) Create a table to store employee details. Define input parameters within the CREATE
PROCEDURE statement and pass them in the CALL statement.
4) create a row-level trigger for the STUDENT table that would get executed by the DML
statement like UPDATE, INSERT or DELETE on that table. The trigger will compute and
show the age difference between current and previous values.
5) creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or
DELETE operations performed on the CUSTOMERS table. This trigger will display the
salary difference between the old values and new values. customer table will have the
attributes-id, name, age, address, salary.
6) Create an implicit Cursor to update the table and increase salary of each customer by 5000.
Determine the number of rows affected (use SQL%ROWCOUNT attribute.)
7) Write a SQL statement
a) to rename the table countries to country_new.
b) to add a column ‘no_of _states’ to the country_new.
c) change the data type of the column country_id to integer in the table locations.
d) to add a primary key for a combination of columns location_id and country_id.
8) Write a SQL statement
a) to add a primary key for a combination of columns location_id and country_id.
b) to drop the existing primary from the table locations on a combination of columns
location_id and country_id.
c) to add a foreign key on job_id column of job_history table referencing to the primary
key job_id of jobs table.
9) A) Write a SQL statement to change salary of employee to 8000 whose ID is 105, if
the existing salary is less than 5000.
B) change job_title of employee which ID is 118, to SH_CLERK if the employee
belongs to department, which ID is 30 and the existing job_title does not start with SH.
10) Write a SQL statement to increase the salary of employees under the department 40, 90 and
110 according to the company rules that, salary will be increased by 25% for the department
40, 15% for department 90 and 10% for the department 110 and the rest of the departments
will remain same
11) Sample table attributes:
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER |
HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID |
DEPARTMENT_ID |
a) Write a query to list the number of jobs available in the employees table.
b) to get the total salaries payable to employees.
c) to get the minimum salary from employees table
d) to get the maximum salary of an employee working as a Programmer.
e) to get the average salary and number of employees working the department 90.
12) Sample table attributes: EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL |
PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT |
MANAGER_ID | DEPARTMENT_ID |
a) Write a query to get the difference between the highest and lowest salaries.
b) to get the department ID and the total salary payable in each department.
c) to get the average salary for each job ID excluding programmer.
d) to get the total salary, maximum, minimum, average salary of employees (job ID wise), for
department ID 90 only.
13) Create the table RIVERS having attributes name, length and outflow. Name must not be
repeated and should have a value. Add a new column called MaxDepth to the rivers table.
14) Create table EMPLOYEE with attributes E_id, E_name, E_dept, E_salary, E_pno, E_city.
Create view having E_id, E_name, E_dept, E_salary.
15) Display name, credit_rating, sales_rep_id from S_customer table of those customer who
either satisfies the condition that credit_rating is greater than 5 out of 10 and sales_rep_id is
equal to 4232.
16) Display the id, name and phone number of the customer 1) Whose id falls in the range 303 to
306
2) Whose id is greater than 300 and customer belongs to Pune.
17) Create a student table, showing the records of the students having
a. Highest marks in math
b. Lowest attendance
c. Total number of students
d. Average marks of dbms
18) Modifying tables using ALTER TABLE command
Adding not null constraint
Removing not null constraint
Adding a new column
19) Consider the below table for reference while trying to solve the SQL queries for practice.
Table – EmployeeDetails
EmpId FullName ManagerId DateOfJoining City
121 John Snow 321 01/31/2019 Toronto
321 Walter White 986 01/30/2020 California
421 Kuldeep Rana 876 27/11/2021 New Delhi
a) Write an SQL query to fetch the EmpId and FullName of all the employees working
under the Manager with id – ‘986’.
b) Write an SQL query to fetch the employees whose name begins with any two
characters, followed by a text “hn” and ends with any sequence of characters.
c) Write an SQL query to fetch the employee’s full names and replace the space with ‘-
’.
20) create a simple stored procedure for adding two numbers and call the procedure so that the
code will be executed.
