Q1. Create a user called universityDB+last 4 digits of roll number.
Ex: universityDB0001
( Then connect psql terminal with this user )
Q2. Create the database called university.
Q3. Connect the database using the following command.
\c university;
Q4. Create the tables in the university database using DDL.sql file.
\i path_to_file\DDL.sql
Q5. Load the data into tables using insert.sql ( This will take some time as the data is very huge
)
\i path_to_file\insert.sql
( we have shared these .sql files on moodle)
Q6. Get the details of the table using information_schema.
(instructions are given at the end of this document )
Q7 Install pgAdmin tool (if not installed) for interacting with postgresql using a GUI interface. Spend some time learning various features of pgAdmin.
( we have shared the documentation on pgAdmin on moodle )
Q8. Execute the following queries :
Find the course id, title, instructor id and name of those instructors who are from CSE department but are teaching a course of Civil department in the year 2009. Arrange results in ascending order of instructor names.
Add a new course with course_id as cs333 (with suitable values for other attributes) for the CSE department and which will have cs303 as a prerequisite. Write insert statements for the same.
Update salaries of instructors by 10% if their departments have a budget of more than 900000 rupees.Write the update statements for the same.
Find CSE department courses (id and title) and number of students taking that course in the year 2007 and semester Fall where the number is greater than 15. Arrange the results in ascending order of course id.
Q9. Try the various queries covered in the class.
Information schema
To list all tables in information_schema.
Ex: SELECT table_name FROM information_schema.tables
WHERE table_schema='public';
To list details of specific table using information_schema.
Ex : SELECT table_name, column_name, data_type FROM
information_schema.columns WHERE
