GATEWAY INSTITUTE OF ENGINEERING
& TECHNOLOGY, SONEPAT
Computer Science & Engineering
(CSE) Department
DBMS Lab
(CSE 383C)
Submitted by: Submitted To:
Kartik Chauhan Ms. Priya Tyagi
23010001023 Asst. Prof. CSE
INDEX
[Link]. Program List Sign
1. Write a PostgreSQL statement to create a table named employees
2. Write a PostgreSQL statement to add a new column email
VARCHAR(100) to the table employees.
3. Write an PostgreSQL statement to drop the table named employees from
the database.
4. Write an PostgreSQL statement to remove all records from the
employees table but keep the table structure intact.
5. A university maintains a database of student records. Initially, the
students table was created without specifying a primary key. The
university now wants to enforce uniqueness for each student by setting
the student_id column as the primary key.
6. In a mystical kingdom, there is a table artifacts that originally used
artifact_name as the primary key. However, the kingdom now wishes to
change the table so that artifact_code becomes the primary key instead,
while keeping artifact_name as a regular column. Additionally, there is
another table artifact_details that holds detailed descriptions about each
artifact. The artifact_details table needs to be updated to ensure that it
references the artifact_code from the artifacts table, using a foreign key
constraint. Write PostgreSQL queries to achieve both tasks mentioned
above.
7. You've been hired to help design a database for a wedding guest list. The
bride and groom need to track their guests, their emails, and meal
preferences.
8. Create a Table with name orders according to as per given constraints.
9. Write a PostgreSQL statement to insert a new employee into the
employees table with the following details:
10. You have a table named employees with the following columns:
employeeid, firstname, lastname, birthdate, hiredate, and salary. Write
the PostgreSQL command to find all employees with a salary greater
than 60000.
11. You have a table named employees with the following columns:
employeelD, firstName, lastName, birthDate, hireDate, and salary.
Write a PostgreSQL statement to update the salary of the employee
whose employeelD is 3 to 52000.00.
12. You have a table named employees with the following columns:
employeeid, firstname, lastname, birthdate, hiredate, and salary.
Write an PostgreSQL statement to delete the record of the employee
with employeeid 3.
13. A Tours and Travels Booking System maintains data of customer
bookings as shown in the below table travel_bookings. You need to
perform the following tasks: Retrieve the names of customers
(customer_name) whose destination starts with “P”. Retrieve all the
booking details for customers with names ending in 'a' using *.
14. A Music Album Management System needs to store details about
different albums and their genres in thealbums table. You need to
perform the following queries: Retrieve album details where the genre
is either 'Rock' OR 'Jazz'. Retrieve album details where the release_year
is greater than 2000 AND the genre is either 'Pop' OR 'Hip-Hop'.
Retrieve album details where: 1. The release_year is greater than 2010
AND 2. The genre is 'Electronic', OR 3. The album_name starts with the
letter 'S'.
15. A table named student_marks has been created to store the details of
students, including their student_id, student_name, and marks. The
marks column has a constraint that ensures values are between 0 and
100. Using this table, perform the following task to demonstrate your
understanding of transactions in SQL:
16. You are managing the database for an airline's check-in system. The
airline keeps track of passenger checkins in a table named boarding_log.
The structure of the table is as follows: log_id (Serial, Primary Key) •
passenger_name (VARCHAR) flight_number (VARCHAR) status
(VARCHAR) - can be 'Checked-In', 'Boarded', or 'Cancelled'
17. Write a SQL statement to select sname from the table sstud1 that is also
present in sstud2 by using a nested query.
18. Write an SQL query to merge sailor information with their respective
mission assignments using sailor_id. Utilize an inner join to combine
sailor profiles from the sailors table with mission records from the
missions table and the result should be displayed with sailor_id, email,
address, mission_date, total_cost.
19. A startup called CodeTantra tracks its product development lifecycle
with a focus on sustainability. They manage three key aspects:
Innovations - Contains details about eco-friendly products. • Materials -
Lists sustainable raw materials used in these products. Research Grants
- Contains funding details for product research and development.
20. A travel agency named "XYZ Adventures" manages bookings,
customers, and tour packages. They keep track of the following tables:
customers: Information about their registered customers. bookings:
Details of tour bookings made by the customers. tour_packages:
Information about the tour packages they offer.
21. Imagine you're working for a school that maintains two separate databases
for two different classes: Class A and Class B. •Class A stores basic
information about students such as their name and marks. Class B stores
additional details about the students, including their address and age. The
school administration wants a comprehensive list of students by performing
a full outer join between these two classes based on their id. The goal is to
retrieve a report that contains the following information: • Student Name
from Class Al Marks from Class A Address from Class B Age from Class
B Can you write an SQL query to produce this report for the school
administration?
22. Write an SQL query to perform a natural join between the two tables
police_officers and officer_details based on the common column
officer_id. The query should return the following columns: officer_id
officer_name badge_number precinct years_of_service
23. Write a PostgreSQL query to list the names and ratings of employees whose
rating is less than their salary divided by 10000. Use a Theta Join with the
<= (less than or equal to) comparison operator to perform this query.
24. Write an SQL query to calculate the CROSS JOIN of the two tables
(police_officers and officer_details). Provide the result with columns:
officer_id, officer_name, badge_number, precinct, and years_of_service.
25. Write a PostgreSQL statement to create a view named stu_view that
displays the name and roll number of the student RAM from the table stu
26. Create the following a view named operations: employee_city_view eTan
that displays the ename and ecity of employees. • Update the ecity for the
employee named 'John' to 'Delhi'. • Create a view named employee_details
that joins employees and departments using dept_id that displays the
employee's name (ename), city (ecity), salary (salary), and department
name (dname) as dept_name. Increase the salary of all employees by 1000
in the employees table where dname = IT.
27. Create a view for employees as high_salary_employees with name, salary
that only allows insertion of employees with a salary greater than 1000,
using WITH CHECK OPTION.
28. The task requires creating an index named category_index on the category
column in the products table. This index aims to improve the efficiency of
queries dealing with product categories.
29. You are a database administrator, and you've identified a need to create a
unique index on the 'email' column of the 'customers' table to ensure that
each customer has a unique email address. You need to create a unique
index with the name customer_email_unique
30. You are a database administrator at CodeKraft, and you have identified a
query that frequently filters and sorts data based on both the 'category' and
'price' columns in the 'products' table. To optimize the query, you need to
create a multi-column index named 'category_price_index' on these two
columns. You need to create an SQL command to create a multi-column
index named 'category_price_index' on the 'category' and 'price' columns
of the 'products' table.
31. You are working on optimizing the database schema for an e-commerce
website. There is a need to create an index on a specific column in one of
the tables to improve query performance. So, your task is to create an index
named'price_index' on the 'price' column in the 'products' table.
32. Write the SQL commands to: Create an index named idx_grade on the
grade column of the students table. Cluster the students table using the
newly created idx_grade index.
33. Create a non-clustered index named idx_product_name on the name
column of the products table.
34. Write an SQL query that converts the hire_date column from a string to a
date using the TO_DATE function. The hire_date is stored as a string in the
format 'YYYYMMDD', and you need to convert it to the standard date
format 'YYYY-MM-DD'.
35. Write a PostgreSQL query to retrieve all records from the order_summary
table where the orders were placed more than 25 days after June 1, 2024.
Use the order_date column for the comparison and display all columns of
the matching records.
36. Write a query to use the LENGTH function to find the number of characters
in each name value.
37. Write a query to use the UPPER function to convert all characters in the
name value to uppercase.
38. Write a query to use the LOWER function to convert all characters in the
name value to lowercase.