In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text.  Here is the table structure: CREATE TABLE USER_ (   USER_ID INT NOT NULL AUTO_INCREMENT, CREATE_DATE DATE NOT NULL, PROF_DESC VARCHAR (100), PROF_PIC VARCHAR(40), LOCATION VARCHAR (40) NOT NULL,       PRIMARY KEY (USER_ID) );  CREATE TABLE USER_INFO( USER_ID INT NOT NULL, SU_EMAIL CHAR(18), USER_FNAME VARCHAR(15), USER_LNAME VARCHAR (15), USER_DOB DATE, USER_GENDER CHAR(1), PRIMARY KEY (USER_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE STUDENT (   SU_ID INT NOT NULL, USER_ID INT NOT NULL, YEAR_ VARCHAR (10), MAJOR VARCHAR (20), LOCATION VARCHAR (40) NOT NULL,      PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE INSTRUCTOR (   SU_ID INT NOT NULL, USER_ID INT NOT NULL, DEPARTMENT VARCHAR (40), INSTR_TYPE VARCHAR (40),      PRIMARY KEY (SU_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE ); CREATE TABLE GROUP_(   GROUP_ID INT NOT NULL AUTO_INCREMENT, GROUP_NAMEVARCHAR(50) NOT NULL, CREATE_DATE DATE NOT NULL,      PRIMARY KEY (GROUP_ID) ); CREATE TABLE MEMBERSHIP (   GROUP_ID INT NOT NULL, USER_ID INT NOT NULL, MEMBER_DESC VARCHAR (10),      PRIMARY KEY (GROUP_ID, USER_ID), FOREIGN KEY (GROUP_ID) REFERENCES GROUP_(GROUP_ID) ON UPDATE CASCADE, FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE );    CREATE TABLE MEDIA( MEDIA_ID VARCHAR(40) NOT NULL, MEDIA_TYPE CHAR(1) NOT NULL, MEDIA_TITTLE VARCHAR(50),      PRIMARY KEY (MEDIA_ID) ); CREATE TABLE POST(   POST_ID CHAR(4) NOT NULL, USER_ID INT NOT NULL, MEDIA_ID VARCHAR(40) NOT NULL, POST_DATE DATE NOT NULL,      PRIMARY KEY (POST_ID), FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE, FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE ); CREATE TABLE VIDEO(   MEDIA_ID VARCHAR(40), V_LENGTH INT, V_DESCRIPTION VARCHAR (100),      PRIMARY KEY (MEDIA_ID), FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE );   CREATE TABLE PHOTO(   MEDIA_ID VARCHAR(40), P_RESOLUTION INT, P_CAPTION VARCHAR (100),      PRIMARY KEY (MEDIA_ID), FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE ); CREATE TABLE TEXT_(   MEDIA_ID VARCHAR(40), T_LENGTH INT, T_CONTENT VARCHAR (1000),      PRIMARY KEY (MEDIA_ID), FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE Fot this database write sql code for the following:  (1) Queries involving subqueries (1) Queries involving GROUP BY and HAVING clause (1) Queries involving left outer join or right outer join of two tables

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
icon
Concept explainers
Question

In this database we have users that can be two types students or instructors. They have groups and memberships. Any user can post and the post type can be video, photo or text. 

Here is the table structure:

CREATE TABLE USER_ (

 

USER_ID INT NOT NULL AUTO_INCREMENT,

CREATE_DATE DATE NOT NULL,

PROF_DESC VARCHAR (100),

PROF_PIC VARCHAR(40),

LOCATION VARCHAR (40) NOT NULL,

 

    PRIMARY KEY (USER_ID)

); 

CREATE TABLE USER_INFO(

USER_ID INT NOT NULL,

SU_EMAIL CHAR(18),

USER_FNAME VARCHAR(15),

USER_LNAME VARCHAR (15),

USER_DOB DATE,

USER_GENDER CHAR(1),

PRIMARY KEY (USER_ID),

FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE

);

CREATE TABLE STUDENT (

 

SU_ID INT NOT NULL,

USER_ID INT NOT NULL,

YEAR_ VARCHAR (10),

MAJOR VARCHAR (20),

LOCATION VARCHAR (40) NOT NULL,

    

PRIMARY KEY (SU_ID),

FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE

);

CREATE TABLE INSTRUCTOR (

 

SU_ID INT NOT NULL,

USER_ID INT NOT NULL,

DEPARTMENT VARCHAR (40),

INSTR_TYPE VARCHAR (40),

    

PRIMARY KEY (SU_ID),

FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE

);

CREATE TABLE GROUP_(

 

GROUP_ID INT NOT NULL AUTO_INCREMENT,

GROUP_NAMEVARCHAR(50) NOT NULL,

CREATE_DATE DATE NOT NULL,

    

PRIMARY KEY (GROUP_ID)

);

CREATE TABLE MEMBERSHIP (

 

GROUP_ID INT NOT NULL,

USER_ID INT NOT NULL,

MEMBER_DESC VARCHAR (10),

    

PRIMARY KEY (GROUP_ID, USER_ID),

FOREIGN KEY (GROUP_ID) REFERENCES GROUP_(GROUP_ID) ON UPDATE CASCADE,

FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE

); 

 

CREATE TABLE MEDIA(

MEDIA_ID VARCHAR(40) NOT NULL,

MEDIA_TYPE CHAR(1) NOT NULL,

MEDIA_TITTLE VARCHAR(50),

    

PRIMARY KEY (MEDIA_ID)

);

CREATE TABLE POST(

 

POST_ID CHAR(4) NOT NULL,

USER_ID INT NOT NULL,

MEDIA_ID VARCHAR(40) NOT NULL,

POST_DATE DATE NOT NULL,

    

PRIMARY KEY (POST_ID),

FOREIGN KEY (USER_ID) REFERENCES USER_(USER_ID) ON UPDATE CASCADE,

FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE

);

CREATE TABLE VIDEO(

 

MEDIA_ID VARCHAR(40),

V_LENGTH INT,

V_DESCRIPTION VARCHAR (100),

    

PRIMARY KEY (MEDIA_ID),

FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE

);

 

CREATE TABLE PHOTO(

 

MEDIA_ID VARCHAR(40),

P_RESOLUTION INT,

P_CAPTION VARCHAR (100),

    

PRIMARY KEY (MEDIA_ID),

FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE

);

CREATE TABLE TEXT_(

 

MEDIA_ID VARCHAR(40),

T_LENGTH INT,

T_CONTENT VARCHAR (1000),

    

PRIMARY KEY (MEDIA_ID),

FOREIGN KEY (MEDIA_ID) REFERENCES MEDIA(MEDIA_ID) ON UPDATE CASCADE

Fot this database write sql code for the following: 

(1) Queries involving subqueries
(1) Queries involving GROUP BY and HAVING clause
(1) Queries involving left outer join or right outer join of two tables  

Expert Solution
Overview

In this question we have to write SQL QUERIES on (1) Queries involving subqueries
(2) Queries involving GROUP BY and HAVING clause
(3) Queries involving left outer join or right outer join of two tables 

Let's query and hope this helps if you find any query on solution, utilize threaded question feature.

steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Query Syntax
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education