DBMS Ques
DBMS Ques
DBMS Ques
Based on Emp table Columns are EmpNo, Ename, Job, Salary, Commission, DeptNO Insert 5
records by stroring Null value in some records for commission column.
Sure! I'll provide you with the SQL queries to answer each of your questions based on the given
"Emp" table.
Q1) Get employee no and employee name who works in dept no 10?
```sql
FROM Emp
```
Q2) Display the employee names of those clerks whose salary > 2000?
```sql
SELECT Ename
FROM Emp
```
```sql
FROM Emp
```
Q4) Display all details of employees whose salary between 2000 and 3000?
```sql
SELECT *
FROM Emp
```
Q5) Display all details of employees whose dept no is 10, 20, or 30?
```sql
SELECT *
FROM Emp
```
```sql
SELECT Ename
FROM Emp
```
Q7) Display dept no & salary in ascending order of dept no and within each dept no, salary should be
in descending order?
```sql
FROM Emp
```
Q8) Display name of employees having two 'a' or 'A' chars in the name?
```sql
SELECT Ename
FROM Emp
```
Q9) Display the name of the employees whose second char is 'b' or 'B'?
```sql
SELECT Ename
FROM Emp
```
Q10) Display the name of the employees whose first or last char is 'a' or 'A'?
```sql
SELECT Ename
FROM Emp
```
```sql
FROM Emp
```sql
FROM Emp
```
```sql
FROM Emp
```
```sql
FROM dual;
```
```sql
FROM dual;
```
Q16) Display info of 'raj' irrespective of the case in which the data is stored.
```sql
SELECT *
FROM Emp
```
Please note that these queries assume the table name is "Emp" and you have the necessary
privileges to execute these queries in your Oracle SQL database.
LAB 4
Certainly! I can help you create the tables and constraints in Oracle SQL. Here are the SQL statements
to create the tables and enforce the given constraints:
```sql
);
```
```sql
DOI DATE,
DOR DATE,
);
```
```sql
CREATE TABLE st (
Rno NUMBER,
Class CHAR,
Marks NUMBER,
);
```
4. Create table S:
```sql
CREATE TABLE S (
sname VARCHAR2(100),
city VARCHAR2(50)
);
```
5. Create table P:
```sql
CREATE TABLE P (
pname VARCHAR2(100),
color VARCHAR2(50)
);
```
```sql
CREATE TABLE SP (
sno NUMBER,
pno NUMBER,
qty NUMBER,
);
```
```sql
);
```
deptno NUMBER,
);
```
These SQL statements will create the tables with the specified attributes and enforce the given
constraints. Please note that the order in which you execute these statements matters due to the
dependencies between tables and constraints. Make sure to execute the statements in the correct
order.
LAB 1
Here are the SQL statements to perform the tasks you've mentioned in Oracle SQL:
```sql
Rno NUMBER,
Name VARCHAR2(100),
DOB DATE,
Gender VARCHAR2(10),
Class VARCHAR2(20),
College VARCHAR2(100),
City VARCHAR2(50),
Marks NUMBER,
```
```sql
INSERT INTO Student (Rno, Name, DOB, Gender, Class, College, City, Marks)
VALUES (1, 'John', TO_DATE('2000-01-01', 'YYYY-MM-DD'), 'Male', '12th', 'ABC College', 'Patiala', 85);
INSERT INTO Student (Rno, Name, DOB, Gender, Class, College, City, Marks)
VALUES (2, 'Jane', TO_DATE('2001-02-03', 'YYYY-MM-DD'), 'Female', '11th', 'XYZ College', 'Amritsar',
90);
INSERT INTO Student (Rno, Name, DOB, Gender, Class, College, City, Marks)
INSERT INTO Student (Rno, Name, DOB, Gender, Class, College, City, Marks)
VALUES (4, 'Sarah', TO_DATE('2003-07-23', 'YYYY-MM-DD'), 'Female', '12th', 'PQR College', 'Patiala',
95);
INSERT INTO Student (Rno, Name, DOB, Gender, Class, College, City, Marks)
VALUES (5, 'Michael', TO_DATE('2001-09-10', 'YYYY-MM-DD'), 'Male', '11th', 'DEF College', 'Amritsar',
80);
```
```sql
```
4. Display the detailed structure of the Student table:
```sql
DESCRIBE Student;
```
```sql
```
```sql
```
```sql
```
```sql
```
```
```sql
```
These SQL statements will help you perform the specified tasks in Oracle SQL. Remember to execute
them in the correct order and make sure to handle sensitive data appropriately.
PREVIOUS YEAR
Q1
(a) Write a SQL sub query using given Student schema to find those students who scores less than
the average score and studies in a department with any student whose last_name does not contain a
character 'n'. Student{Stu_id, First_name, Last_name, Join_date, Score, Department_id} (6+4)
(b) (i) Write a SQL query using given Emp scheme to fetch Emp_id and Salary of the employees,
where the address is Kolkata.
(ii) Write a SQL query using given Emp scheme to find the Emp_name who earns more than the
average salary Em p{Emp_id, Emp_name, Salary, Address}
ANS
(a) SQL subquery to find students with scores less than the average and studying in a department
without a 'n' in any student's last_name:
```sql
FROM Student
AND Department_id IN (
SELECT Department_id
FROM Student
WHERE Last_name NOT LIKE '%n%'
);
```
(b) (i) SQL query to fetch Emp_id and Salary of employees with address 'Kolkata':
```sql
FROM Emp
```
(ii) SQL query to find Emp_name who earns more than the average salary:
```sql
SELECT Emp_name
FROM Emp
```
These SQL queries should provide you with the desired results based on the given schemas and
conditions. Remember to replace table and column names with the appropriate names in your actual
database.
Q 2 a) Discuss the difference/s between following terms with example/SQL syntax i) Primary key and
Unique key
b) Write a SQL sub query using given employee schema to find those employees who earn less than
the average salary and work in a department with any employee whose last_name not contains a
character 'n'. Emp{Emp_id,First_name,Last_name,Hire_date,Job_id,Salary,Department_id}
ANS
a)
- Primary key: A primary key is a column or a set of columns that uniquely identifies each row in a
table. It enforces entity integrity and ensures that there are no duplicate or null values in the
specified column(s). Each table can have only one primary key.
```sql
First_name VARCHAR(50),
Last_name VARCHAR(50),
-- Other columns
);
```
- Unique key: A unique key is a constraint that ensures that the values in a column or a set of
columns are unique, i.e., no duplicate values are allowed. Unlike a primary key, a table can have
multiple unique keys.
```sql
Emp_id INT,
First_name VARCHAR(50),
Last_name VARCHAR(50),
-- Other columns
UNIQUE (Emp_id)
);
```
- Drop: The DROP statement is used to remove an entire table or a specific database object (e.g.,
index, view, procedure) from the database.
```sql
```
- Truncate: The TRUNCATE statement is used to remove all rows from a table, effectively resetting the
table to its initial state. Unlike the DROP statement, it keeps the table structure intact.
```sql
```
- On Delete Set null: This refers to a referential integrity constraint that, when a referenced row is
deleted, sets the foreign key values in the referencing table to NULL. This action allows the
referencing rows to remain in the table but with the foreign key values pointing to NULL.
```sql
```
- On Delete Set Cascade: This refers to a referential integrity constraint that, when a referenced row
is deleted, automatically deletes all referencing rows in the dependent table. This ensures the
integrity of the relationship between the tables.
```sql
ON DELETE CASCADE;
```
- Inner Join: An inner join returns only the matching rows between two tables based on the specified
join condition. It combines rows from both tables that satisfy the join condition.
```sql
FROM Employee
```
- Outer Join: An outer join returns both matching and non-matching rows from the tables involved in
the join. It includes all the rows from one table and the matching rows from the other table. If there
are no matches, NULL values are included for the non-matching rows.
```sql
FROM Employee
```
v) Composite and Multivalued attributes:
- Composite attribute: A composite attribute is an attribute that can be further divided into sub-
attributes. It represents a group of related attributes that form a part of a larger attribute. For
example, a "Name" attribute can be divided into "First Name," "Last Name," and "Middle Name" sub-
attributes.
Example:
```sql
Emp_id INT,
First_name VARCHAR(50),
Last_name VARCHAR(50),
-- Other columns
);
```
- Multivalued attribute: A multivalued attribute is an attribute that can have multiple values for a
single entity or row. It represents a set of values rather than a single value. For example, an "Email"
attribute can have multiple email addresses associated with it for a single employee.
Example:
```sql
Emp_id INT,
First_name VARCHAR(50),
Last_name VARCHAR(50),
-- Other columns
);
```
b) SQL subquery to find employees who earn less than the average salary and work in a department
with any employee whose last_name does not contain a character 'n':
```sql
FROM Emp
AND Department_id IN (
SELECT Department_id
FROM Emp
);
```
This SQL subquery retrieves the employee ID, first name, and last name from the "Emp" table where
the salary is less than the average salary and the department ID is present in the subquery result,
which checks for departments with employees whose last names do not contain the character 'n'.
QUES 3
QUES 4
Q1) Answer each of the following questions briefly. The questions are based on the following
relational schema: Student( Rno. Name) Course(CNo, Cname) Teacher(Tno, Tname)
Registration(Rno,Cno, Tno
) i.) Find the name of students taking course number UCS3 10.
iv.) Display all student names who has character 'a' in their names
. v.) Find the course name taught by teacher Raj. Query should able to find all teachers with this
name irrespective of the case.
ANS
Q1) Answer each of the following questions briefly. The questions are based on the
following relational schema:
To find the name of students taking a specific course, we need to join the Student and
Registration tables based on the Rno (student number) and then filter the result based
on the desired course number.
```sql
SELECT Name
FROM Student
JOIN Registration ON Student.Rno = Registration.Rno
JOIN Course ON Registration.Cno = Course.CNo
WHERE Cname = 'UCS310';
```
ii) Find the teacher name teaching the course name DBMS.
To find the teacher's name teaching a specific course, we need to join the Teacher and
Registration tables based on the Tno (teacher number) and then filter the result based
on the desired course name.
```sql
SELECT Tname
FROM Teacher
JOIN Registration ON Teacher.Tno = Registration.Tno
JOIN Course ON Registration.Cno = Course.CNo
WHERE Cname = 'DBMS';
```
To find the number of students in each course, we need to group the records by the
course and then count the distinct student numbers (Rno).
```sql
SELECT Course.Cname, COUNT(DISTINCT Registration.Rno) AS Num_of_Students
FROM Course
JOIN Registration ON Course.CNo = Registration.Cno
GROUP BY Course.Cname;
```
iv) Display all student names who have the character 'a' in their names.
To display all student names that contain the character 'a', we can use the LIKE operator
with the '%' wildcard to match any characters before and after the 'a'.
```sql
SELECT Name
FROM Student
WHERE Name LIKE '%a%';
```
To find the course name taught by a teacher named Raj, regardless of case, we can use
the UPPER or LOWER functions to convert both the teacher's name and the search term
to uppercase or lowercase.
```sql
SELECT Course.Cname
FROM Course
JOIN Registration ON Course.CNo = Registration.Cno
JOIN Teacher ON Registration.Tno = Teacher.Tno
WHERE UPPER(Teacher.Tname) = UPPER('Raj');
```
Q2) On the same database given in Q1, solve the following queries:
```sql
ALTER TABLE Student
ADD mobile_number VARCHAR(15);
```
ii) Write the command to create the Registration table by identifying primary and foreign
keys.
```sql
CREATE TABLE Registration (
Rno INT,
Cno INT,
Tno INT,
PRIMARY KEY (Rno, Cno, Tno),
FOREIGN KEY (Rno) REFERENCES Student (Rno),
FOREIGN KEY (Cno) REFERENCES Course (CNo),
FOREIGN KEY (Tno) REFERENCES Teacher (Tno)
);
```
iii) Change the mobile number and name of the student having roll number 5.
```sql
UPDATE Student
SET Name = 'New Name', mobile_number = '1234567890'
WHERE Rno = 5;
```
iv) Display Tno and Name of teachers who take more than 2 courses.
```sql
SELECT Teacher.Tno, Teacher.Tname
FROM Teacher
JOIN Registration ON Teacher.Tno = Registration.Tno
GROUP BY Teacher.Tno, Teacher.Tname
HAVING COUNT(DISTINCT Registration.Cno) > 2;
```
their corresponding course name. Also display the name of teachers taking no courses.
```sql
SELECT Teacher.Tname, Course.Cname
FROM Teacher
LEFT JOIN Registration ON Teacher.Tno = Registration.Tno
LEFT JOIN Course ON Registration.Cno = Course.CNo;
```
The above query performs a left join between the Teacher table and the Registration
table, and then joins the Course table to get the corresponding course name. This will
include all teachers, even those who are not teaching any courses (null values in the
Course table).
QUES 5
Write SQL queries for the following statements based on Employees table whose 4
schema is given below: (Employee_l D,F
irst_Name,Last_Name,Email,Phone_Number,HireDate,Jobl D, Sala ry, Manager_Id,
Department_Id)
a. Create the given table along with the following constraints: Phone_Number should
not be left undefined, salary should be between 10000 and 20000, employee_id
should uniquely identify rows and should not be left undefined. User defined names
should be given to constraints
. b. Display each employee's last name, hire date and salary review date (which is the
date after six months of service). Label the column as "REVIEW".
c. Display the manager number and salary of the lowest paid employee for that
manager. Exclude anyone whose manager is not known. Exclude any groups where the
minimum salary is 6000 or less. Sort the output in descending order of salary.
d. Get First_Name and Last_Name as single column from employees table separated by
a’_’
ANS
```sql
First_Name VARCHAR(50),
Last_Name VARCHAR(50),
Email VARCHAR(100),
HireDate DATE,
Job_Id VARCHAR(20),
Manager_Id INT,
Department_Id INT,
);
```
In the above query, we created the Employees table with the specified columns and
applied the following constraints:
- The Phone_Number column is set as NOT NULL using the NOT NULL constraint.
- The Salary column has a CHECK constraint to ensure its value is between 10000 and
20000.
- The Employee_Id column is specified as the primary key using the PRIMARY KEY
constraint.
- The Phone_Number column has a unique constraint using the UNIQUE constraint.
- The Employee_Id column has a constraint to check that it is not NULL using the CHECK
constraint.
b. Displaying each employee's last name, hire date, and salary review date:
```sql
FROM Employees;
```
In the above query, we selected the Last_Name, HireDate, and calculated the review
date by adding 6 months to the HireDate using the INTERVAL '6' MONTH syntax.
c. Displaying the manager number and salary of the lowest paid employee for each
manager:
```sql
FROM Employees
GROUP BY Manager_Id
In the above query, we selected the Manager_Id and used the MIN() function to
calculate the lowest salary for each manager. We excluded anyone whose manager is not
known (NULL) and any groups where the minimum salary is 6000 or less. The result is
sorted in descending order of the lowest salary.
```sql
FROM Employees;
```
In the above query, we used the CONCAT() function to concatenate the First_Name and
Last_Name columns with an underscore (_) separator. The result is labeled as Full_Name.
QUES 5
Write SQL queries for the following: i) Find the names of all clients having 'a' as the
second letter in their name. ii) Display the order number and day on which client placed
their order. iii) List the names, city and state of clients who are not in the state of
'Maharashtra'. iv) Print the description and total quantity sold for each product. v)
Retrieve the product no and total quantity ordered for products 'P0001' and 'P0002'
from sales_order_details.
ANS
i) Find the names of all clients having 'a' as the second letter in their name.
```sql
SELECT name
FROM client_master
```
ii) Display the order number and day on which client placed their order.
```sql
FROM sales_order;
```
iii) List the names, city, and state of clients who are not in the state of 'Maharashtra'.
```sql
FROM client_master
```
iv) Print the description and total quantity sold for each product.
```sql
FROM product_master
GROUP BY description;
```
v) Retrieve the product number and total quantity ordered for products 'P0001' and
'P0002' from sales_order_details.
```sql
FROM sales_order_details
GROUP BY product_no;
```
Note: The given queries are based on the provided schema, but it's important to ensure
the correctness of table and column names as well as their relationships in your actual
database.
QUES 6
Write a SQL Queries for the given three table schema (104 Teacher(Teacher id:string
Primary key,teacher_name: string. eacher_add:string.teacher_telno:integer)
Student(Student_id:number Primary key,student_name:string,student add:string)
Evaluation( leacher id:string. student id:integer.date_a_evaluation:date. marks:integer))
i. Display the name of the Teacher whose name starts with character A. ii. Write a query
to change the date of evaluation of student having id 101 and teacher id is T103 from 23
MARCH, 2017 to 23 JULY. 2017. iii. Calculate the average marks given by the teacher. Also
print the id and name of the Teacher. iv. Display the name of the Student who has
secured highest marks v. Display the name of the teacher with their telephone number
who has evaluated only one student vi. Display the names of the students and their
marks who have been evaluated by more than one Teacher vii. Display the Teacher id
and name with their student count viii. Find the id of teacher who has not evaluated any
of the students. ix. Display the names of the Student whose date of evaluation lies in the
month of Max.'. x. Display Student Name and their teacher id. even if the student has
not been evaluated by any teacher.
ANS
Q6(a)
i. Display the name of the Teacher whose name starts with character A.
```sql
SELECT teacher_name
FROM Teacher
```
ii. Write a query to change the date of evaluation of student having id 101 and teacher id
is T103 from 23 MARCH, 2017 to 23 JULY, 2017.
```sql
UPDATE Evaluation
```
iii. Calculate the average marks given by the teacher. Also print the id and name of the
Teacher.
```sql
FROM Evaluation
```
iv. Display the name of the Student who has secured the highest marks.
```sql
SELECT student_name
FROM Student
WHERE student_id = (
SELECT student_id
FROM Evaluation
WHERE marks = (
SELECT MAX(marks)
FROM Evaluation
)
);
```
v. Display the name of the teacher with their telephone number who has evaluated only
one student.
```sql
FROM Teacher
WHERE teacher_id IN (
SELECT teacher_id
FROM Evaluation
GROUP BY teacher_id
);
```
vi. Display the names of the students and their marks who have been evaluated by more
than one Teacher.
```sql
FROM Student
WHERE student_id IN (
SELECT student_id
FROM Evaluation
GROUP BY student_id
);
```
vii. Display the Teacher id and name with their student count.
```sql
FROM Teacher
```
viii. Find the id of the teacher who has not evaluated any of the students.
```sql
SELECT teacher_id
FROM Teacher
FROM Evaluation
);
```
ix. Display the names of the Student whose date of evaluation lies in the month of Max.
```sql
SELECT student_name
FROM Student
FROM Evaluation
);
```
x. Display Student Name and their teacher id even if the student has not been evaluated
by any teacher.
```sql
FROM Student
```
Note: The given queries are based on the provided schema, but it's important to ensure
the correctness of table and column names as well as their relationships in your actual
database.
QUES 7 Differentiate the following with examples: (20) i) DROP and DELETE ii) ON DELETE
SET NULL and ON DELETE CASCADE iii) Entity integrity constraint and Referential Integrity
constraint iv) Outer Join and Inner Join
- DROP is a Data Definition Language (DDL) statement used to remove an entire table or a database
object from the database schema. It permanently deletes the table and all its associated data,
indexes, triggers, and constraints. For example:
```sql
```
- DELETE is a Data Manipulation Language (DML) statement used to remove one or more rows from a
table based on specified conditions. It allows selective deletion of data while keeping the table
structure intact. For example:
```sql
```
ii) ON DELETE SET NULL and ON DELETE CASCADE:
- ON DELETE SET NULL is a referential action that can be specified in a foreign key constraint. When a
row is deleted from the parent table, the corresponding foreign key values in the child table are set
to NULL. For example:
```sql
```
- ON DELETE CASCADE is another referential action specified in a foreign key constraint. When a row
is deleted from the parent table, all corresponding rows in the child table are automatically deleted.
For example:
```sql
ON DELETE CASCADE;
```
- Entity integrity constraint ensures that each row in a table is uniquely identified by a primary key
and that no primary key value is NULL. It guarantees the uniqueness and integrity of the primary key
column(s). For example:
```sql
Column2 VARCHAR(255),
...
);
```
- Referential integrity constraint ensures the consistency and integrity of relationships between
tables. It defines rules for maintaining the relationships between primary key and foreign key
columns. It ensures that foreign key values in a table match the corresponding primary key values in
the referenced table. For example:
```sql
Column1 INT,
Column2 VARCHAR(255),
);
```
- Inner Join is used to combine rows from two or more tables based on a related column between
them. It returns only the matched rows from both tables. For example:
```sql
SELECT *
FROM Table1
ON Table1.CommonColumn = Table2.CommonColumn;
```
- Outer Join is used to combine rows from two or more tables, including unmatched rows, based on a
related column between them. It returns all the rows from one table and the matching rows from the
other table(s). There are three types of outer joins: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL
OUTER JOIN. For example:
```sql
SELECT *
FROM Table1
ON Table1.CommonColumn = Table2.CommonColumn;
```
Note: The examples provided are in SQL syntax, but the concepts apply to other database systems as
well.
QUES 8
Q4: Write relational algebra for given questions. (9) Employee(FName, LName, SSN*, BDate, Address,
Sex, Salary, SuperSSN, Dno) Department(Dnumber* , Dname, MgrSSN) Dept_Location(Dno*,
Dlocation*) Project(Pname, Pnumber*, Plocation, Dno) Works_On(Essn, Pno, Hours)
Dependent(Essn, Depent Name, Sex, Bdate, Relationship) a) Retrieve the names of all employees in
department 5 who work more than 10 hours per week on the 'ProductX' project. b) List the names of
all employees who have a dependent with the same first name as themselves. c) Find the names of
all employees who are directly supervised by 'Franklin Wong'. d) For each project, list the project
name and the total hours per week (by all employees) spent on that project. e) Retrieve the names
of all employees who work on every project. t) Retrieve the names of all employees who do not work
on any project. p g) For each department, retrieve the department name and the average salary of all
employees working in that department. h) Retrieve the average salary of all female employees. i)
Find the names and addresses of all employees who work on at least one project located in Houston
but whose department has no location in Houston
ANS
Certainly! Here are the corresponding SQL queries for the given questions using Oracle SQL syntax:
a) Retrieve the names of all employees in department 5 who work more than 10 hours per week on
the 'ProductX' project.
```sql
FROM Employee
```
b) List the names of all employees who have a dependent with the same first name as themselves.
```sql
FROM Employee
WHERE SSN IN (
SELECT Essn
FROM Dependent
);
```
c) Find the names of all employees who are directly supervised by 'Franklin Wong'.
```sql
FROM Employee
WHERE SuperSSN = (
SELECT SSN
FROM Employee
);
```
d) For each project, list the project name and the total hours per week (by all employees) spent on
that project.
```sql
FROM Project
GROUP BY Pname;
```
```sql
FROM Employee
SELECT Pnumber
FROM Project
SELECT Pno
FROM Works_On
);
```
f) Retrieve the names of all employees who do not work on any project.
```sql
FROM Employee
```
g) For each department, retrieve the department name and the average salary of all employees
working in that department.
```sql
GROUP BY Dname;
```
```sql
FROM Employee
```
i) Find the names and addresses of all employees who work on at least one project located in
Houston but whose department has no location in Houston.
```sql
FROM Employee
SELECT Dno
FROM Dept_Location
);
```