S3
Continue the SQL command to add the following data to an entity with the name COURSE Code: IT414 Title: Database Administration Coordinator: John Doe SQL Command: INSERT INTO COURSE VALUES _________;
('IT414', 'Database Administration', 'AX765')
Continue the SQL command to add data to an entity with the name DEPARTMENT. ID: 101 Name: Computer Science Phone: 899-012-7638 Median salary: 130,245.50 INSERT INTO DEPARTMENT VALUES.__________;
(101, 'Computer Science', '899-012-7638', NULL, 130245.50)
Given the tables: DEPARTMENT(ID INT PK, DNAME VARCHAR(50) NN, PHONE CHAR(12) NN, LAUNCH_DATE DATE, MEDIAN_SALARY DECIMAL(8,2)) INSTRUCTOR(CODE CHAR(5) PK, FIRST_NAME VARCHAR(35) NN, MIDDLE_NAME VARCHAR(35), LAST_NAME VARCHAR(35) NN, DOB DATE NN, DEPARTMENT_ID INT NN FK) Continue the SQL command to create an entity with the name COURSE Specification: A course is identified by a unique code of 5 to 7 characters
CODE VARCHAR(7) PRIMARY KEY
Given the tables: DEPARTMENT(ID INT PK, DNAME VARCHAR(50) NN, PHONE CHAR(12) NN, LAUNCH_DATE DATE, MEDIAN_SALARY DECIMAL(8,2)) INSTRUCTOR(CODE CHAR(5) PK, FIRST_NAME VARCHAR(35) NN, MIDDLE_NAME VARCHAR(35), LAST_NAME VARCHAR(35) NN, DOB DATE NN, DEPARTMENT_ID INT NN FK) Continue the SQL command to create an entity with the name COURSE Specification: A course may have at most one coordinator (an instructor). An instructor may coordinate several courses.
COORDINATOR_CODE CHAR(5)
Given the tables: DEPARTMENT(ID INT PK, DNAME VARCHAR(50) NN, PHONE CHAR(12) NN, LAUNCH_DATE DATE, MEDIAN_SALARY DECIMAL(8,2)) INSTRUCTOR(CODE CHAR(5) PK, FIRST_NAME VARCHAR(35) NN, MIDDLE_NAME VARCHAR(35), LAST_NAME VARCHAR(35) NN, DOB DATE NN, DEPARTMENT_ID INT NN FK) Enter the beginning of the SQL command to create an entity with the name COURSE
CREATE TABLE COURSE
Given the table: DEPARTMENT( ID (INT), DNAME (VARCHAR(50)), PHONE (CHAR(12)), LAUNCH_DATE (DATE), MEDIAN_SALARY (DECIMAL(8,2))) Enter the beginning of the SQL command to create an entity with the name INSTRUCTOR. Write the name of the SQL commands in upper cases.
CREATE TABLE INSTRUCTOR
Enter the SQL command to show the structure of the created table department. Write the name of the SQL commands in upper cases and use the typical naming conventions
DESCRIBE DEPARTMENT;
Enter the SQL command to delete the database named COLLEGE checking if such a database is already defined. Write the name of the SQL commands in upper cases.
DROP DATABASE IF EXISTS COLLEGE;
Given the tables: DEPARTMENT(ID INT PK, DNAME VARCHAR(50) NN, PHONE CHAR(12) NN, LAUNCH_DATE DATE, MEDIAN_SALARY DECIMAL(8,2)) INSTRUCTOR(CODE CHAR(5) PK, FIRST_NAME VARCHAR(35) NN, MIDDLE_NAME VARCHAR(35), LAST_NAME VARCHAR(35) NN, DOB DATE NN, DEPARTMENT_ID INT NN FK) Continue the SQL command to create an entity with the name COURSE Specification: A course may have at most one coordinator (an instructor). An instructor may coordinate several courses. Write the referential constraint
FOREIGN KEY (COORDINATOR_CODE) REFERENCES INSTRUCTOR(CODE)
Continue the SQL command with the column clause to show a list of the first, middle and last names for all course coordinators and the title of the course they coordinate. All joining conditions must be specified in the from clause. SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE __________ condition-clause;
FROM INSTRUCTOR INNER JOIN COURSE ON INSTRUCTOR.CODE=COURSE.COORDINATOR_CODE
Continue the SQL command with the column clause to show a list of the first, middle and last names for all course coordinators and the title of the course they coordinate. Instructors without a coordinated course must be included. SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE __________ condition-clause;
FROM INSTRUCTOR LEFT JOIN COURSE ON INSTRUCTOR.CODE=COURSE.COORDINATOR_CODE
Continue the SQL command with the column clause to show a list of the first, middle and last names for all course coordinators and the title of the course they coordinate. Instructors without a coordinated course must be included. All joining conditions must be specified in the from clause. SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE __________ condition-clause;
FROM INSTRUCTOR LEFT JOIN COURSE ON INSTRUCTOR.CODE=COURSE.COORDINATOR_CODE
Continue the SQL command with the column clause to show a list of the first, middle and last names for their coordinator, if any. Courses without a coordinator must be included SELECT TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME __________ condition-clause;
FROM INSTRUCTOR RIGHT JOIN COURSE ON INSTRUCTOR.CODE=COURSE.COORDINATOR_CODE
Enter the beginning of the SQL command to add data to an entity with the name COURSE.
INSERT INTO COURSE VALUES
Start the SQL command with the column clause to show a list of the names for all the departments. The heading of the list must be: Name. __________ table-clause condition-clause;
SELECT DNAME AS 'Name'
Start the SQL command with the column clause to show a list of the first, middle and last names for all course coordinators and the title of the course they coordinate. __________ table-clause condition-clause;
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE
Start the SQL command with the column clause to show a list of all courses and the first, middle, and last names for their coordinator if any. __________ table-clause condition-clause;
SELECT TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME
Start the SQL command with the column clause to show a list of all the courses and the first, middle, and last names for their coordinator, if any. __________ table-clause condition-clause;
SELECT TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME
Given the tables: DEPARTMENT(ID INT PK, DNAME VARCHAR(50) NN, PHONE CHAR(12) NN, LAUNCH_DATE DATE, MEDIAN_SALARY DECIMAL(8,2)) INSTRUCTOR(CODE CHAR(5) PK, FIRST_NAME VARCHAR(35) NN, MIDDLE_NAME VARCHAR(35), LAST_NAME VARCHAR(35) NN, DOB DATE NN, DEPARTMENT_ID INT NN FK) Continue the SQL command to create an entity with the name COURSE Specification: A course has a title of up to 40 characters. There might be two different courses that have the same name.
TITLE VARCHAR(40) NOT NULL
Continue the SQL command with the column clause to show a list of the first, middle and last names for their coordinator, only for the coordinators having the last name Johnson SQL Command: SELECT TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM INSTRUCTOR INNER JOIN COURSE ON INSTRUCTOR.CODE=COURSE.COORDINATOR_CODE __________;
WHERE LAST_NAME='Johnson'
