YSQL [jsohn6]> SELECT * FROM SCHOOL;lySQL [jsohn6]> SELECT * FROM DEPARTMENT;SCHOOL_CODEDEPARTMENT_ID| DEPARTMENT_ID |DEPARTMENT_NAME101SCHOOL OF MERCHANDISINGFASHION DESIGN101SCHOOL OF FASHION DESIGN102ADVERTISING102103|201||202 |SCHOOL OF GRAPHIC DESIGNGRAPHIC DESIGN103SCHOOL OF FINE ARTS201FINE ARTSSCHOOL OF DESIGN AND TECHNOLOGDESIGN AND TECHNOLOGY202rows in set (0.00 sec)MYSQL [jsohn6]> SELECTFROM PROFESSOR;MYSQL [jsohn6]> SELECT * FROM TECHNOLOGY;| PROF_NUM | PROF_LASTNAMEPROF_FIRSTNAMECOURSE_TYPE | LABTOP_NUMBER1 | Smith2 | Banks3 | Taylor4 | Smith6 | Smith7 | BanksMary| JaneTECH-10012345HazelTECH-11067890TerryTECH-12013579| Jenny| Tim24680TECH-2004 rows in set (0.00 sec)6 rows in set (0.00 sec) VSQL [jsohn6]> SELECTFROM COURSE;COURSE_TYPE | PROF_NUM | DEPARTMENT_ID | COURSE_TITLE| COURSE_DESCRIPTION| COURSE_TYPE_CODE |101 | Costume Designs101 | Couture Fashion101 | Costume Designs101 | Textile Designs201 | Fine Arts103 | Computer Patterns103 | Web Retail103 | Web Design202 | Design with Technolo | Intro to Computer Design| Fundamentals of Costume Design | F| Fundamentals of Luxury Design | F| Fundamentals of Costume Design | F| Fundamentals of Textile Design | FAdvanced Printmaking| Intro to Computer PatternsFASH-100FASH-110FASH-120FASH-150FASH-2104 |2 |4 |2 |TECH-100Intro to Retail EcommerceTECH-110| Fundamentals of Graphic Design | TTECH-120TECH-200rows in set (e.01 sec)MYSQL [jsohn6]> SELECT * FROM FASHION;COURSE_TYPE | RUNWAY_DATE| 2020-05-27| 2020-05-27| 2020-05-27| 2020-05-28| 2020-05-28FASH-100FASH-110FASH-120FASH-150FASH-210rows in set (0.00 sec)

Question
Asked Dec 13, 2019
5 views

Create a complex query based on two or more tables (SCHOOL, DEPARTMENT, PROFESSOR, TECHNOLOGY, COURSE, & FASHION tables attached) which includes either several conditions or a subquery (or both).

YSQL [jsohn6]> SELECT * FROM SCHOOL;
lySQL [jsohn6]> SELECT * FROM DEPARTMENT;
SCHOOL_CODE
DEPARTMENT_ID
| DEPARTMENT_ID |
DEPARTMENT_NAME
101
SCHOOL OF MERCHANDISING
FASHION DESIGN
101
SCHOOL OF FASHION DESIGN
102
ADVERTISING
102
103|
201||
202 |
SCHOOL OF GRAPHIC DESIGN
GRAPHIC DESIGN
103
SCHOOL OF FINE ARTS
201
FINE ARTS
SCHOOL OF DESIGN AND TECHNOLOG
DESIGN AND TECHNOLOGY
202
rows in set (0.00 sec)
MYSQL [jsohn6]> SELECT
FROM PROFESSOR;
MYSQL [jsohn6]> SELECT * FROM TECHNOLOGY;
| PROF_NUM | PROF_LASTNAME
PROF_FIRSTNAME
COURSE_TYPE | LABTOP_NUMBER
1 | Smith
2 | Banks
3 | Taylor
4 | Smith
6 | Smith
7 | Banks
Mary
| Jane
TECH-100
12345
Hazel
TECH-110
67890
Terry
TECH-120
13579
| Jenny
| Tim
24680
TECH-200
4 rows in set (0.00 sec)
6 rows in set (0.00 sec)
help_outline

Image Transcriptionclose

YSQL [jsohn6]> SELECT * FROM SCHOOL; lySQL [jsohn6]> SELECT * FROM DEPARTMENT; SCHOOL_CODE DEPARTMENT_ID | DEPARTMENT_ID | DEPARTMENT_NAME 101 SCHOOL OF MERCHANDISING FASHION DESIGN 101 SCHOOL OF FASHION DESIGN 102 ADVERTISING 102 103| 201|| 202 | SCHOOL OF GRAPHIC DESIGN GRAPHIC DESIGN 103 SCHOOL OF FINE ARTS 201 FINE ARTS SCHOOL OF DESIGN AND TECHNOLOG DESIGN AND TECHNOLOGY 202 rows in set (0.00 sec) MYSQL [jsohn6]> SELECT FROM PROFESSOR; MYSQL [jsohn6]> SELECT * FROM TECHNOLOGY; | PROF_NUM | PROF_LASTNAME PROF_FIRSTNAME COURSE_TYPE | LABTOP_NUMBER 1 | Smith 2 | Banks 3 | Taylor 4 | Smith 6 | Smith 7 | Banks Mary | Jane TECH-100 12345 Hazel TECH-110 67890 Terry TECH-120 13579 | Jenny | Tim 24680 TECH-200 4 rows in set (0.00 sec) 6 rows in set (0.00 sec)

fullscreen
VSQL [jsohn6]> SELECT
FROM COURSE;
COURSE_TYPE | PROF_NUM | DEPARTMENT_ID | COURSE_TITLE
| COURSE_DESCRIPTION
| COURSE_TYPE_CODE |
101 | Costume Designs
101 | Couture Fashion
101 | Costume Designs
101 | Textile Designs
201 | Fine Arts
103 | Computer Patterns
103 | Web Retail
103 | Web Design
202 | Design with Technolo | Intro to Computer Design
| Fundamentals of Costume Design | F
| Fundamentals of Luxury Design | F
| Fundamentals of Costume Design | F
| Fundamentals of Textile Design | F
Advanced Printmaking
| Intro to Computer Patterns
FASH-100
FASH-110
FASH-120
FASH-150
FASH-210
4 |
2 |
4 |
2 |
TECH-100
Intro to Retail Ecommerce
TECH-110
| Fundamentals of Graphic Design | T
TECH-120
TECH-200
rows in set (e.01 sec)
MYSQL [jsohn6]> SELECT * FROM FASHION;
COURSE_TYPE | RUNWAY_DATE
| 2020-05-27
| 2020-05-27
| 2020-05-27
| 2020-05-28
| 2020-05-28
FASH-100
FASH-110
FASH-120
FASH-150
FASH-210
rows in set (0.00 sec)
help_outline

Image Transcriptionclose

VSQL [jsohn6]> SELECT FROM COURSE; COURSE_TYPE | PROF_NUM | DEPARTMENT_ID | COURSE_TITLE | COURSE_DESCRIPTION | COURSE_TYPE_CODE | 101 | Costume Designs 101 | Couture Fashion 101 | Costume Designs 101 | Textile Designs 201 | Fine Arts 103 | Computer Patterns 103 | Web Retail 103 | Web Design 202 | Design with Technolo | Intro to Computer Design | Fundamentals of Costume Design | F | Fundamentals of Luxury Design | F | Fundamentals of Costume Design | F | Fundamentals of Textile Design | F Advanced Printmaking | Intro to Computer Patterns FASH-100 FASH-110 FASH-120 FASH-150 FASH-210 4 | 2 | 4 | 2 | TECH-100 Intro to Retail Ecommerce TECH-110 | Fundamentals of Graphic Design | T TECH-120 TECH-200 rows in set (e.01 sec) MYSQL [jsohn6]> SELECT * FROM FASHION; COURSE_TYPE | RUNWAY_DATE | 2020-05-27 | 2020-05-27 | 2020-05-27 | 2020-05-28 | 2020-05-28 FASH-100 FASH-110 FASH-120 FASH-150 FASH-210 rows in set (0.00 sec)

fullscreen
check_circle

Expert Answer

Step 1

CREATE TABLE:

Use the statement CREATE TABLE to create a new SQL table.

Syntax:

CREATE TABLE { table-name } (     { column1-name } datatype, { column2-name }  datatype,     { column3-name } datatype,    .... );

INSERT INTO:

Use the statement INSERT INTO to insert new records into a table.

Syntax:

INSERT INTO { table-name }  ({column1-name }, { column2-name }, { column3-name }, ...) VALUES ({column1-value}, { column2-value }, { column3-value }, ...);

SELECT:

SELECT is used to display the records from the table.

 

JOIN:

 

JOIN is used based on a column related to each other to combine rows from two or more tables. Based on the same column name, it is used to link the table.

 

Syntax:

SELECT { column-name }

FROM { table1-name } 

JOIN { table2-name }ON { table1-name }.{ column-name }= { table2-name } . { column-name };

 

IN:

 

IN operator is used where specific multiple values are searched for.

 

Syntax:

SELECT {column(s)-name }

FROM {table1-name }

WHERE {column-name } IN (Value1, Value2, ...);

 

Step 2

Create the table SCHOOL:

CREATE TABLE SCHOOL (
SCHOOL_CODE VARCHAR(100),
DEPARTMENT_ID
);

Inserting data in SCHOOL table:

//Inserting data as provided in the given table

INSERT INTO SCHOOL(SCHOOL_CODE,DEPARTMENT_ID)VALUES("SCHOOL OF MERCHANDISING",101);

 

//Inserting data as provided in the given table

INSERT INTO SCHOOL(SCHOOL_CODE,DEPARTMENT_ID)VALUES("SCHOOL OF FASHION DESIGN",102);

 

//Inserting data as provided in the given table

INSERT INTO SCHOOL(SCHOOL_CODE,DEPARTMENT_ID)VALUES("SCHOOL OF GRAPHIC DESIGN",103);

 

//Inserting data as provided in the given table

INSERT INTO SCHOOL(SCHOOL_CODE,DEPARTMENT_ID)VALUES("SCHOOL OF FINE ARTS",201);

 

//Inserting data as provided in the given table

INSERT INTO SCHOOL(SCHOOL_CODE,DEPARTMENT_ID)VALUES("SCHOOL OF DESIGN AND TECHNOLOGY",202);

 

help_outline

Image Transcriptionclose

Number of Records: 5 SCHOOL_CODE DEPARTMENT_ID SCHOOL OF MERCHANDISING 101 SCHOOL OF FASHION DESIGN 102 SCHOOL OF GRAPHIC DESIGN 103 SCHOOL OF FINE ARTS 201 SCHOOL OF DESIGN AND TECHNOLOGY 202

fullscreen
Step 3

Create the table DEPARTMENT:

CREATE TABLE DEPARTMENT (
DEPARTMENT_NAME VARCHAR(100),
DEPARTMENT_ID INT
);

Inserting data in DEPARTMENT table:

//Inserting data as provided in the given table

INSERT INTO DEPARTMENT(DEPARTMENT_NAME,DEPARTMENT_ID)VALUES("FASHION DESIGN",101);

 

//Inserting data as provided in the given table

INSERT INTO DEPARTMENT(DEPARTMENT_NAME,DEPARTMENT_ID)VALUES("ADVERTISING",102);

//Inserting data as provided in the given table

INSERT INTO DEPARTM...

help_outline

Image Transcriptionclose

Number of Records: 5 DEPARTMENT_NAME DEPARTMENT_ID FASHION DESIGN 101 ADVERTISING 102 GRAPHIC DESIGN 103 FINE ARTS 201 DESIGN AND TECHNOLOGY 202

fullscreen

Want to see the full answer?

See Solution

Check out a sample Q&A here.

Want to see this answer and more?

Solutions are written by subject experts who are available 24/7. Questions are typically answered within 1 hour.*

See Solution
*Response times may vary by subject and question.
Tagged in

Engineering

Computer Science

Related Computer Science Q&A

Find answers to questions asked by student like you
Show more Q&A
add
question_answer

Q: What are different datatypes used in c?

A: Definition of data type –Data types in C language are used to declare functions or to declare variab...

question_answer

Q: Now add the-dflag, and change the number of loops (-l) from 1 to highernumbers. What happens? Does t...

A: Answer:The program does not always deadlock. The user could not get the code to deadlock until he ra...

question_answer

Q: I have having some confusion as to what exactly the defintion of  "special operator in the condition...

A: The Definition of “Special Operator in The Conditional Expression” is:In the question a query based ...

question_answer

Q: (1) Prompt the user for a title for data. Output the title. Ex: Enter a title for the data: Number o...

A: Answer:1.Program: #include <iostream>#include <string> using namespace std; int main(){ ...

question_answer

Q: The prompt asks me to design a program that asks the user to enter a month (in numeric form), a day,...

A: Following is the c++ program which takes input from user the day, month and year of date. If else co...

question_answer

Q: Write a C++ program that computes a student’s grade for an assignment as a percentage given the stud...

A: Instructions for the given program:For the given program, there should exist a file with the name “R...

question_answer

Q: Write a c++ program   The above figure represents relationships between different struct variables a...

A: Program:#include<iostream>#include<string> using namespace std; struct Driver{  string N...

question_answer

Q: 1:02 PM QWhat is your question? B Consigerrtne two nOstS A aNa B are connected through a direct link...

A: Given data:Bandwidth of the link = 10bits/sec.Propagation speed = 2x10 m/secDistance = 5000 kmConver...

question_answer

Q: 1. Explain what the CPU should do when an interrupt occurs. Include in your answer the method the CP...

A: Click to see the answer