Database Technology - postgresql

manpreet Best Answer 3 years ago

I am quite new to postgresql. Could any expert help me solve this problem please.

Consider the following PostgreSQL tables created for a university system recording which students take which modules:

CREATE TABLE module (id bigserial, name text);
CREATE TABLE student (id bigserial, name text);
CREATE TABLE takes (student_id bigint, module bigint);

1. Rewrite the SQL to include sensible primary keys.

   CREATE TABLE module
   (
   m_id bigserial,
   name text,
   CONSTRAINT m_key PRIMARY KEY (m_id)
   );
   
   CREATE TABLE student
   (
   s_id bigserial,
   name text
   CONSTRAINT s_key PRIMARY KEY (s_id)
   );
   
   CREATE TABLE takes
   (
   student_id bigint,
   module bigint,
   CONSTRAINT t_key PRIMARY KEY (student_id)
   );

Given this schema I have the following questions:

1. Write an SQL query to count how many students are taking DATABASE.

   SELECT COUNT(name) 
   FROM student
   WHERE module = 'DATABASE' AND student_id=s_id 

2. Write an SQL query to show the student IDs and names (but nothing else) of all student taking DATABASE

   SELECT s_id, name
   FROM Student, take 
   WHERE module = 'DATABASE' AND student_id = s_id

3. Write an SQL query to show the student IDs and names (but nothing else) of all students not taking DATABASE.

   SELECT s_id, name
   FROM Student, take 
   WHERE student_id = s_id AND module != 'DATABASE'

Above are my answers. Please correct me if I am wrong and please comment the reason. Thank you for your expertise.