Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

Database Technology - postgresql

General Tech Technology & Software

Max. 2000 characters
Replies

usr_profile.png

User

( 3 months 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.

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif