IT 214 part 2

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 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;

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 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_NAME FROM EMPLOYEE WHERE SALARY<=(SELECT AVG(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_NAME FROM EMPLOYEE WHERE SALARY=(SELECT MIN(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);

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_NAME FROM PRODUCT WHERE 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_NAME FROM PRODUCT WHERE 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 lowest salary of all employees.

SELECT MIN(SALARY) FROM EMPLOYEE;

Write the complete SQL command to compute the total to be paid to all employees.

SELECT SUM(SALARY) FROM EMPLOYEE;