IT 214 part 1
Continue the SQL command to add the following data to an entity with the name COURSE.
('IT414', 'Database Administration', 'AX765')
Specification: A course is identified by a unique code of 5 to 7 characters. SQL Command: CREATE TABLE COURSE ( ________________________ , .... .... .... ) ;
CODE VARCHAR(7) PRIMARY KEY
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 ) CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITLE VARCHAR(40) NOT NULL , ________________________ , .... .... .... ) ; Specification: A course may have at most one coordinator (an instructor). An instructor may coordinate several courses. Write only the definition of the attribute without any referential constraint. (Hint. when writing remember that we're referencing this part later on but not now)
COORDINATOR_CODE CHAR(5)
Specification: A course may have at most one coordinator (an instructor). An instructor may coordinate several courses. Write only the definition of the attribute without any referential constraint. SQL Command: CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITLE VARCHAR(40) NOT NULL , ________________________ , .... .... .... ) ;
COORDINATOR_CODE CHAR(5)
Enter the SQL command to add the database named COLLEGE. Write the name of the SQL commands in upper cases.
CREATE DATABASE COLLEGE;
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
Enter the beginning of the SQL command to create an entity with the name INSTRUCTOR. ________________________ (.... .... .... ) ;
CREATE TABLE INSTRUCTOR
Enter the SQL command to delete the database named COLLEGE if such a database is already defined. Write the name of the SQL commands in upper cases.
DROP DATABASE IF EXISTS COLLEGE;
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 ) CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , TITLE VARCHAR(40) NOT NULL , COORDINATOR_CODE CHAR(5) , ________________________ , .... .... .... ) ; Specification: A course may have at most one coordinator (an instructor). An instructor may coordinate several courses. Write only the referential constraint.<- actual question
FOREIGN KEY (COORDINATOR_CODE) REFERENCES INSTRUCTOR(CODE)
Given the table: DEPARTMENT( ID (INT), DNAME (VARCHAR(50)), PHONE (CHAR(12)), LAUNCH_DATE (DATE), MEDIAN_SALARY (DECIMAL(8,2))) SQL Command: 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, ________________________ , .... .... .... ) ; Specification: An instructor must belong to a department. There are many instructors in the same department.
FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(ID)
Continue the SQL command with the table clause to show a list of the names for all the departments. The heading of the list must be: Name. Complete only the part marked with ____ . Use upper case for SQL keywords. SELECT DNAME AS 'Name' _____________________ condition-clause ;
FROM DEPARTMENT
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. Complete only the part marked with ____ . Use upper case for SQL keywords. Include only what is needed, avoid specifying optional elements. SQL Command: SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE _____________________ condition-clause;
FROM INSTRUCTOR INNER JOIN COURSE ON INSTRUCTOR.CODE=COURSE.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.
FROM INSTRUCTOR INNER JOIN COURSE ON INSTRUCTOR.CODE=COURSE.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 course they coordinate, if any. Instructors without a coordinated course must be included. All joining conditions must be specified in the from clause. Do not use aliases for tables.
FROM INSTRUCTOR LEFT JOIN COURSE ON INSTRUCTOR.CODE=COURSE.CODE
Continue 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. Courses without a coordinator must be included. All joining conditions must be specified in the from clause. Do not use aliases for tables. Complete only the part marked with ____ . Use upper case for SQL keywords. Include only what is needed, avoid specifying optional elements. SQL Command: SELECT TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME _____________________ condition-clause;
FROM INSTRUCTOR RIGHT JOIN COURSE ON INSTRUCTOR.CODE=COURSE.CODE
Specification: A department has the primary key ID, which is an integer. SQL Command: CREATE TABLE DEPARTMENT ( ________________________ , .... .... .... ) ;
ID INT PRIMARY KEY
Enter the beginning of the SQL command to add data to an entity with the name COURSE.
INSERT INTO COURSE VALUES
Some departments have a median salary specified. All salaries are under one million dollars and we represent them using the standard representation with 2 digits for cents. (Note: use a two words attribute name).
MEDIAN_SALARY DECIMAL(8,2)
Enter the SQL command to show all the data in the table department.
SELECT * FROM DEPARTMENT;
Write the complete SQL command to count all the employees that have a middle name.
SELECT COUNT(MIDDLE_NAME) FROM EMPLOYEE;
Write the complete SQL command to list the frequency of all the date of birth, most frequent first and chronologically for same frequency (i.e. for each date of birth show how many persons have that date of birth).
SELECT DATE_OF_BIRTH, COUNT(*) FROM PERSON GROUP BY DATE_OF_BIRTH ORDER BY COUNT(*) DESC, DATE_OF_BIRTH;
Write the complete SQL command to list the frequency of all the dates of birth (i.e. for each date of birth show how many persons have that date of birth).
SELECT DATE_OF_BIRTH, COUNT(*) FROM PERSON GROUP BY DATE_OF_BIRTH;
Write the complete SQL command to list, in alphabetic order, the frequency of all the first names (i.e. for each first name show how many persons have that first name).
SELECT FIRST_NAME, COUNT(*) FROM PERSON GROUP BY FIRST_NAME ORDER BY FIRST_NAME;
Write the complete SQL command to list the first, middle and last name of all employees with the lowest salary.
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM EMPLOYEE WHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEE);
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. Complete only the part marked with ____ . Use upper case for SQL keywords. Include only what is needed, avoid specifying optional elements. SQL Command: _____________________ table-clause condition-clause;
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE
___ is a computer language for storing, manipulating and retrieving data stored in a relational database.
SQL
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
A course has a title of up to 40 characters. There might be two different courses that have the same name. SQL Command: CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , ________________________ , .... .... .... ) ;
TITLE VARCHAR(40) NOT NULL
Specification: A course has a title of up to 40 characters. There might be two different courses that have the same name. SQL Command: CREATE TABLE COURSE ( CODE VARCHAR(7) PRIMARY KEY , ________________________ , .... .... .... ) ;
TITLE VARCHAR(40) NOT NULL
Continue the SQL command with the condition clause to show a list of all courses and the first, middle and last names for their coordinator, only for the coordinators having the last name Johnson. All joining conditions must be specified in the from clause. Do not use aliases for tables. Complete only the part marked with ____ . Use upper case for SQL keywords. Include only what is needed, avoid specifying optional elements. SQL Command: SELECT TITLE, FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM INSTRUCTOR INNER JOIN COURSE ON COURSE.CODE=INSTRUCTOR.CODE _____________________ ;
WHERE LAST_NAME= 'Johnson'
