It 214 final commands

Given the tables CREATE TABLE CLASS ( CODE CHAR(2) PRIMARY KEY , LEVEL VARCHAR(10) NOT NULL , COLOR VARCHAR(10) NOT NULL , ROOM INT NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , CAPACITY INT NOT NULL , LOCATION VARCHAR(100) ); CREATE TABLE STUDENT ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(30) NOT NULL , MIDDLE NAME VARCHAR(30) , LAST_NAME VARCHAR(30) NOT NULL , NICKNAME VARCHAR(15) , DATE_OF_BIRTH DATE NOT NULL , STREET VARCHAR(50) NOT NULL , CITY VARCHAR(25) NOT NULL , ZIPCODE VARCHAR(10) NOT NULL , STATE CHAR(2) NOT NULL , CLASS_CODE CHAR(2) NOT NULL REFERENCES CLASS(CODE) ); Continue the SQL command with the condition clause that will list the names of the students (first, middle, last) with their date of birth for all the students born in March (in order to prepare a special celebration) SQL COMMAND SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DATE_OF_BIRTH FROM STUDENT __________ condition clause

WHERE MONTH(DATE_OF_BIRTH)=3

Given the following tables CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , LAUNCH_DATE DATE , MEDIAN_SALARY DECIMAL(8, 2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY , FIRST_NAME VARCHAR(35) NOT NULL , MIDDLE_NAME VARCHAR(35) , LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL , DEPARTMENT_ID INT NOT NULL FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); Continue the SQL command to add data to an entity with the name INSTRUCTOR. Write the name of the SQL command in upper case. Complete only the part marked with ____ from the command. Use single quotes for String literals. Department - id: 101 - Name: Computer Science - Phone: 899-012-7638 - Median salary: 130 245 50 Instructor - Code: AX765 - First name: John - Last name: Doe - DOB: 4/7/1980 - Department: Computer Science INSERT INTO INSTRUCTOR VALUES

('AX765', 'John', NULL, 'Doe', '1980-04-07', 101)

Given the tables CREATE TABLE CLASS ( CODE CHAR(2) PRIMARY KEY , LEVEL VARCHAR(10) NOT NULL , COLOR VARCHAR(10) NOT NULL , ROOM INT NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , CAPACITY INT NOT NULL , LOCATION VARCHAR(100) ); CREATE TABLE STUDENT ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(30) NOT NULL , MIDDLE NAME VARCHAR(30) , LAST_NAME VARCHAR(30) NOT NULL , NICKNAME VARCHAR(15) , DATE_OF_BIRTH DATE NOT NULL , STREET VARCHAR(50) NOT NULL , CITY VARCHAR(25) NOT NULL , ZIPCODE VARCHAR(10) NOT NULL , STATE CHAR(2) NOT NULL , CLASS_CODE CHAR(2) NOT NULL REFERENCES CLASS(CODE) ); Continue the SQL command with any other needed condition clause for the query that will list the first and last name of the monkey students (their class level is "monkey") in dictionary order with the room number of their class SQL COMMAND SELECT FIRST_NAME, LAST_NAME, ROOM FROM STUDENT, CLASS WHERE CLASS_CODE = CODE _____ condition clause

AND LEVEL='monkey'

Given the following tables CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , LAUNCH_DATE DATE , MEDIAN_SALARY DECIMAL(8, 2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY , FIRST_NAME VARCHAR(35) NOT NULL , MIDDLE_NAME VARCHAR(35) , LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL , DEPARTMENT_ID INT NOT NULL FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITEL VARCHAR(40) NOT NULL , COORDINATOR_CODE CHAR(5) , 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 instructors and the title of the courses they coordinate, if any instructors without a coordinatored ocurse must be included complete only the part marked with ________ SQL Command SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE ________________ condition-clause

FROM INSTRUCTOR LEFT JOIN COURSE ON INSTRUCTOR.CODE=COURSE.COORDINATOR_CODE

Given the tables CREATE TABLE CLASS ( CODE CHAR(2) PRIMARY KEY , LEVEL VARCHAR(10) NOT NULL , COLOR VARCHAR(10) NOT NULL , ROOM INT NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , CAPACITY INT NOT NULL , LOCATION VARCHAR(100) ); CREATE TABLE STUDENT ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(30) NOT NULL , MIDDLE NAME VARCHAR(30) , LAST_NAME VARCHAR(30) NOT NULL , NICKNAME VARCHAR(15) , DATE_OF_BIRTH DATE NOT NULL , STREET VARCHAR(50) NOT NULL , CITY VARCHAR(25) NOT NULL , ZIPCODE VARCHAR(10) NOT NULL , STATE CHAR(2) NOT NULL , CLASS_CODE CHAR(2) NOT NULL REFERENCES CLASS(CODE) ); Continue the SQL command with the sorting clause that will list the names of the students (first, middle, last) is dictionary order with their nicknames for all the students enrolled in the green monkeys class (the class code is GM) SQL Command SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, NICKNAME FROM STUDENT WHERE CLASS_CODE='GM' ORDEr _____

ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME;

Given the tables CREATE TABLE CLASS ( CODE CHAR(2) PRIMARY KEY , LEVEL VARCHAR(10) NOT NULL , COLOR VARCHAR(10) NOT NULL , ROOM INT NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , CAPACITY INT NOT NULL , LOCATION VARCHAR(100) ); Continue the SQL command to store the referential constraint for the attribute in the specification (do not use a seperate constraint, but use a constratint clause for the attribute); A class has up to 30 students enrolled. A student will be enrolled in a single class. SQL Command CREATE TABLE STUDENT ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(30) NOT NULL , MIDDLE NAME VARCHAR(30) , LAST_NAME VARCHAR(30) NOT NULL , NICKNAME VARCHAR(15) , DATE_OF_BIRTH DATE NOT NULL , STREET VARCHAR(50) NOT NULL , CITY VARCHAR(25) NOT NULL , ZIPCODE VARCHAR(10) NOT NULL , STATE CHAR(2) NOT NULL , CLASS_CODE CHAR(2) NOT NULL _________________________________

REFERENCES CLASS(CODE)

CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY , DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , LAUNCH_DATE DATE , MEDIAN_SALARY DECIMAL(8, 2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY , FIRST_NAME VARCHAR(35) NOT NULL , MIDDLE_NAME VARCHAR(35) NOT NULL , LAST_NAME VARCHAR(35) NOT NULL , DOB DATE NOT NULL , DEPARTMENT_ID INT NOT NULL , FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITLE VARCHAR(40) NOT NULL , COORDINATOR_CODE CHAR(5) , FOREIGN KEY (COORDINATOR_CODE) REFERENCES INSTRUCTOR(CODE) ) Write the complete SQL command to count all the courses coordinators SQL Command SELECT FROM

SELECT COUNT(DISTINCT COORDINATOR_CODE) FROM COURSE;

Given the following table CREATE TABLE PRODUCT ( ID INT PRIMARY KEY , SHORT_NAME VARCHAR(15) NOT NULL , PRICE DECIMAL(7, 2) , QUANTITY INT NOT NULL ), Write the complete SQL command to count all the products that have a price specified

SELECT COUNT(PRICE) FROM PRODUCT;

Given the following table CREATE TABLE PERSON ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(25) NOT NULL , MIDDLE_NAME VARCHAR(25) NOT NULL , LAST_NAME VARCHAR(25) NOT NULL , DATE_OF_BIRTH DATE NOT NULL DATE_OF_DEATH DATE ); Write the complete SQL command to list the frequency if all the date of death. most frequent first and chronologically for same frequency. (ie for each date of death show how many persons have that date of death) SELECT FROM GROUP ORDER

SELECT DATE_OF_DEATH, COUNT(*) FROM PERSON GROUP BY DATE_OF_DEATH ORDER BY COUNT(*) DESC, DATE_OF_DEATH;

Given the tables CREATE TABLE CLASS ( CODE CHAR(2) PRIMARY KEY , LEVEL VARCHAR(10) NOT NULL , COLOR VARCHAR(10) NOT NULL , ROOM INT NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , CAPACITY INT NOT NULL , LOCATION VARCHAR(100) ); CREATE TABLE STUDENT ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(30) NOT NULL , MIDDLE NAME VARCHAR(30) , LAST_NAME VARCHAR(30) NOT NULL , NICKNAME VARCHAR(15) , DATE_OF_BIRTH DATE NOT NULL , STREET VARCHAR(50) NOT NULL , CITY VARCHAR(25) NOT NULL , ZIPCODE VARCHAR(10) NOT NULL , STATE CHAR(2) NOT NULL , CLASS_CODE CHAR(2) NOT NULL REFERENCES CLASS(CODE) ); Start the SQL command with the column clause that will list the names of the students (first, middle, last) with their date of birth for all students born in MARCH (in order to prepare a special celebration) SQL command

SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DATE_OF_BIRTH

Given the tables CREATE TABLE CLASS ( CODE CHAR(2) PRIMARY KEY , LEVEL VARCHAR(10) NOT NULL , COLOR VARCHAR(10) NOT NULL , ROOM INT NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , CAPACITY INT NOT NULL , LOCATION VARCHAR(100) ); CREATE TABLE STUDENT ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(30) NOT NULL , MIDDLE NAME VARCHAR(30) , LAST_NAME VARCHAR(30) NOT NULL , NICKNAME VARCHAR(15) , DATE_OF_BIRTH DATE NOT NULL , STREET VARCHAR(50) NOT NULL , CITY VARCHAR(25) NOT NULL , ZIPCODE VARCHAR(10) NOT NULL , STATE CHAR(2) NOT NULL , CLASS_CODE CHAR(2) NOT NULL REFERENCES CLASS(CODE) ); Start the SQL command with the column clause that will list the names of the students (first, middle, last) in dictionary order with their nicknames for all the students enrolled in the green monkeys class (the class code is GM) SQL command

SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, NICKNAME

Given the following tables CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , LAUNCH_DATE DATE , MEDIAN_SALARY DECIMAL(8, 2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY , FIRST_NAME VARCHAR(35) NOT NULL , MIDDLE_NAME VARCHAR(35) , LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL , DEPARTMENT_ID INT NOT NULL FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITEL VARCHAR(40) NOT NULL , COORDINATOR_CODE CHAR(5) , FOREIGN KEY (COORDINATOR_CODE) REFERENCES INSTRUCTOR(CODE) ) 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 SQL Command _______________________ table-clause condition-clause

SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE

Given the following table CREATE TABLE PERSON ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(25) NOT NULL , MIDDLE_NAME VARCHAR(25) NOT NULL , LAST_NAME VARCHAR(25) NOT NULL , DATE_OF_BIRTH DATE NOT NULL DATE_OF_DEATH DATE ); Write the complete SQL command to list the frequency of all the last names, most frequent lists and alphabetically for same frequency (ie for each last anme show many persons have that last name) SELECT FROM GROUP ORDER

SELECT LAST_NAME, COUNT(*) FROM PERSON GROUP BY LAST_NAME ORDER BY COUNT(*) DESC, LAST_NAME

CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY , DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , LAUNCH_DATE DATE , MEDIAN_SALARY DECIMAL(8, 2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY , FIRST_NAME VARCHAR(35) NOT NULL , MIDDLE_NAME VARCHAR(35) NOT NULL , LAST_NAME VARCHAR(35) NOT NULL , DOB DATE NOT NULL , DEPARTMENT_ID INT NOT NULL , FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITLE VARCHAR(40) NOT NULL , COORDINATOR_CODE CHAR(5) , FOREIGN KEY (COORDINATOR_CODE) REFERENCES INSTRUCTOR(CODE) ) Write the complete SQL command to list all the department names with the total number of instructors employed SQL COMMAND SELECT FROM WHERE GROUP ORDER

SELECT DNAME, COUNT(*) FROM DEPARTMENT, INSTRUCTOR WHERE ID = DEPARTMENT_ID GROUP BY DNAME ORDER BY DNAME;

Given the following table CREATE TABLE EMPLOYEE ( ID INT PRIMARY KEY , FIRST_NAME VARCHAR(25) NOT NULL , MIDDLE_NAME VARCHAR(25) , LAST_NAME VARCHAR(25) NOT NULL , SALARY DECIMAL(7, 2) NOT NULL , DEPENDENTS INT NOT NULL ); Write the complete SQL command to compute the total to be paid to all employees

SELECT SUM(SALARY) FROM EMPLOYEE;

Given the following tables CREATE TABLE DEPARTMENT ( ID INT PRIMARY KEY DNAME VARCHAR(50) NOT NULL UNIQUE , PHONE CHAR(12) NOT NULL UNIQUE , LAUNCH_DATE DATE , MEDIAN_SALARY DECIMAL(8, 2) ); CREATE TABLE INSTRUCTOR ( CODE CHAR(5) PRIMARY KEY , FIRST_NAME VARCHAR(35) NOT NULL , MIDDLE_NAME VARCHAR(35) , LAST_NAME VARCHAR(35) NOT NULL, DOB DATE NOT NULL , DEPARTMENT_ID INT NOT NULL FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)); Finish the SQL command with the condition clause if needed to show a list of the first, middle, and last names for all instructors and their department names. Complete only the part marked with___ Use upper case for SQL keywords SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, DNAME FROM DEPARTMENT, INSTRUCTOR _________________________________________

WHERE DEPARTMENT_ID=ID;