no
Continue the SQL command to add the following data to an entity with the name COURSE.
('IT414', 'Database Administration', 'AX765')
Relationships in ER model are usually expressed in second person singular.
Wrong
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 SQL command to delete the database named COLLEGE if such a database is already defined
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
Given the following table: 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) ); 0 out of 0.06 point 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
resolves uncertainty and represents processed data
Information resolves uncertainty and represents processed data.
Specification: A student is enrolled in many classes. The above specification indicates a relationship having the type
It is a MANY to MANY relationship between student and class because a class contain MANY students (common sense assumption) and a student is enrolled in many classes (given).
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)
median salary one hundrer thousands
MEDIAN_SALARY>100000;
is the notation for a relational database.
RDB is the notation for a relational database.
Write the complete SQL command to compute the average salary of all employees
SELECT AVG(SALARY) FROM EMPLOYEE;
Write the complete SQL command to count all the products.
SELECT COUNT(*) FROM PRODUCT;
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;
Enter the complete SQL command to delete a SQL database named GMU, not giving an error if there is no such database: ______ Use uppercase for SQL reserved words.
DROP DATABASE IF EXISTS GMU;
Write the complete SQL command to count all distinct first names.
SELECT COUNT(DISTINCT FIRST_NAME) FROM EMPLOYEE;
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 count all the products that have a price specified.
SELECT COUNT(PRICE) FROM PRODUCT;
Write the complete SQL command to count all the different short names of products.
SELECT COUNT(SHORT_NAME) FROM PRODUCT;
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 PERSONGROUP BY DATE_OF_BIRTH;
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 PERSONGROUP BY DATE_OF_BIRTHORDER BY COUNT(*) DESC, DATE_OF_BIRTH;
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
Enter the beginning of the SQL command to add data to an entity with the name INSTRUCTOR. Write the name of the SQL commands in upper cases. Co only the part marked with from the command.
INSERT INTO INSTRUCTOR VALUES
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-clausecondition-clause;
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME, TITLE
Write the complete SQL command to list the first, middle and last name of all employees that have a salary at most the average salary of all the employees.
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAMEFROM EMPLOYEEWHERE SALARY<=(SELECT AVG(SALARY) FROM EMPLOYEE);
Write the complete SQL command to list the first, middle and last name of all employees with the highest salary.
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAMEFROM EMPLOYEEWHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE);
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_NAMEFROM EMPLOYEEWHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEE);
Write the complete SQL command to list the first, middle and last name of all employees with the lowest salary.[select] # columns clause[from] # tables clause[where] # condition clause
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAMEFROM EMPLOYEEWHERE SALARY=(SELECT MIN(SALARY) FROM EMPLOYEE);
In a database we store user data and metadata
Right
Enter the SQL command to show all the data in the table department.
SELECT * FROM DEPARTMENT;
write the sql command to count all the employees
SELECT COUNT(*) FROM EMPLOYEE
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 PERSONGROUP BY FIRST_NAMEORDER BY FIRST_NAME;
show a list of the first, middle, last name and title
SELECT FIRST_NAME,MIDDLE,LAST, TITLE
CREATE TABLE PRODUCT (ID INT PRIMARY KEY ,SHORT_NAME VARCHAR(15) NOT NULL ,LONG_NAME VARCHAR(50) NOT NULL UNIQUE ,PRICE DECIMAL(7,2) ,QUANTITY INT NOT NULL );Write the complete SQL command to list the long names of all the products that have a price at most the average price of all the products..
SELECT LONG_NAMEFROM PRODUCTWHERE PRICE<=(SELECT AVG(PRICE) FROM PRODUCT);
Write the complete SQL command to list the long names of all the products that have the the highest price of all the products.[select] # columns clause[from] # tables clause[where] # condition clause
SELECT LONG_NAMEFROM PRODUCTWHERE PRICE=(SELECT MAX(PRICE) FROM PRODUCT);
Write the complete SQL command to compute the highest salary of all employees.Do not use aliases. Do not include a condition clause.
SELECT MAX(SALARY) FROM EMPLOYEE;
Write the complete SQL command to compute the total to be paid to all employees.
SELECT SUM(SALARY) FROM EMPLOYEE;
the entity has a ____ primary key_______ ID______
TRAINER, surrogate primary key, there is no good candidate key
The domain (set of values) of a(n) may represent an entity itself (e.g. an address of a person, a city in which a person is living, the phone number of a person).
The domain (set of values) of an attribute may represent an entity itself (e.g. an address of a person, a city in which a person is living, the phone number of a person).
Write the complete SQL command to compute the lowest salary of all employees.
SELECT MIN(SALARY) FROM EMPLOYEE;
___ 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_NAMEFROM INSTRUCTOR INNER JOIN COURSE ON COURSE.CODE=INSTRUCTOR.CODE_____________________ ;
WHERE LAST_NAME= 'Johnson'
The___________ model represents data in tuples grouped in relations
relational The relational model represents data in tuples grouped in relations. A tuple contains the value predetined data type (i.e. domain).
A database organized in terms of the relational model is a relational database.
right
a relationship degree indicates
the number of entities or participants associated with a relationship
Partial/totaldisjoint/overlap# of discriminatorsAttribute nameattribute domainatt value
total disjoint single TYPECHAR(1) D,N,S
The following clause is specifying a 1:M relationship between the DEPARTMENT(CODE, NAME) and EMPLOYEE tables: CREATE TABLE EMPLOYEE( ID INT PRIMARY KEY, NAME VARCHAR (50) NOT NULL DEPARTMENT CODE NOT NULL FOREIGN KEY (DEPARTMENT_CODE) );
wrong
Enter the beginning of the SQL command to create an entity with the name INSTRUCTOR.________________________ (.... .... .... ) ;
CREATE TABLE INSTRUCTOR
