Question

Create table student with required attribute and perform following query:

 
 
Select S_NAME from STUDENT where S_ID IN
(Select S_ID from STUDENT_COURSE where C_ID IN
(SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));

Answer 1

To create the STUDENT table with the required attributes:

CREATE TABLE STUDENT (
  S_ID INT PRIMARY KEY,
  S_NAME VARCHAR(50) NOT NULL,
  S_EMAIL VARCHAR(50) UNIQUE,
  S_PHONE VARCHAR(20)
);

To perform the given query:

SELECT S_NAME
FROM STUDENT
WHERE S_ID IN (
  SELECT S_ID
  FROM STUDENT_COURSE
  WHERE C_ID IN (
    SELECT C_ID
    FROM COURSE
    WHERE C_NAME = 'DSA' OR C_NAME = 'DBMS'
  )
);

In this query, we are selecting the names of the students who have enrolled in either the "DSA" or "DBMS" courses. The subquery (SELECT C_ID FROM COURSE WHERE C_NAME = 'DSA' OR C_NAME = 'DBMS') selects the IDs of the courses "DSA" and "DBMS", and the subquery (SELECT S_ID FROM STUDENT_COURSE WHERE C_ID IN (subquery)) selects the IDs of the students who are enrolled in these courses. Finally, we use the outer query to select the names of these students from the STUDENT table. sample database

CREATE TABLE Student (
  S_ID INT PRIMARY KEY,
  S_NAME VARCHAR(50) NOT NULL,
  S_EMAIL VARCHAR(50),
  S_PHONE VARCHAR(20)
);

CREATE TABLE Course (
  C_ID INT PRIMARY KEY,
  C_NAME VARCHAR(50) NOT NULL,
  C_CREDIT INT
);

CREATE TABLE Student_Course (
  S_ID INT,
  C_ID INT,
  PRIMARY KEY(S_ID, C_ID),
  FOREIGN KEY(S_ID) REFERENCES Student(S_ID),
  FOREIGN KEY(C_ID) REFERENCES Course(C_ID)
);

INSERT INTO Student VALUES (1, 'John Doe', 'johndoe@example.com', '123-456-7890');
INSERT INTO Student VALUES (2, 'Jane Smith', 'janesmith@example.com', '987-654-3210');
INSERT INTO Student VALUES (3, 'Bob Johnson', 'bobjohnson@example.com', '555-555-5555');

INSERT INTO Course VALUES (1, 'DSA', 3);
INSERT INTO Course VALUES (2, 'DBMS', 4);
INSERT INTO Course VALUES (3, 'Algorithms', 3);

INSERT INTO Student_Course VALUES (1, 1);
INSERT INTO Student_Course VALUES (1, 2);
INSERT INTO Student_Course VALUES (2, 1);
INSERT INTO Student_Course VALUES (2, 3);
INSERT INTO Student_Course VALUES (3, 2);
INSERT INTO Student_Course VALUES (3, 3);