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 inner join on two or more tables (1) Queries involving aggregate functions, such as SUM, COUNT, AVG etc.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
In this
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 inner join on two or more tables
(1) Queries involving aggregate functions, such as SUM, COUNT, AVG etc.
Step by step
Solved in 3 steps
