SQL Practice - Submit SQL statements Using the tables (cs3350.branches, cs3350.t
ID: 3816451 • Letter: S
Question
SQL Practice -
Submit SQL statements
Using the tables (cs3350.branches, cs3350.transactions, cs3350.employees, cs3350.customers)
CS3350.EMPLOYEES
( EMPLOYEE_ID NUMBER,
BRANCH NUMBER,
FIRST_NAME VARCHAR2,
LAST_NAME VARCHAR2
)
CS3350.CUSTOMERS
( CUSTOMER_ID NUMBER,
PRIMARY_BRANCH NUMBER,
FIRST_NAME VARCHAR2,
LAST_NAME VARCHAR2
)
CS3350.TRANSACTIONS
( TRANSACTION_ID NUMBER,
CUSTOMER_ID NUMBER,
BRANCH_NUMBER NUMBER,
AMOUNT NUMBER(6,2),
TRANSACTION_DATE DATE
)
CS3350.BRANCHES
( BRANCH_NUMBER NUMBER,
STREET VARCHAR2,
CITY VARCHAR2,
ZIPCODE VARCHAR2
)
write Select SQL statements to retrieve the following information.
The first and last name of all employees at branch_id - 12349, listed in alphabetical order by last name.
The first and last name of the employees with the following employee_ids - 2,3,5,7,11,13 listed in alphabetical order by last name.
All street addresses, city, and zip_code and the number of employees at that branch listed in order by most employees to the least, then by street address.
The street address, city and zip_code of all branches with 3 or more customers listing that branch as their primary branch listed in order by street address.
The date as Month Name Day, Year (January 24, 2017), and amount of all transactions performed on a Tuesday listed in ascending date order.
Explanation / Answer
1. SELECT FIRST_NAME,LAST_NAME FROM CS3350.EMPLOYEES WHERE BRANCH=12349 ORDER BY LAST_NAME;
2.SELECT FIRST_NAME,LAST_NAME FROM CS3350.EMPLOYEES WHERE EMPLOYEE_ID IN (2,3,5,7,11,13) ORDER BY LAST_NAME;
3.SELECT STREET,CITY,ZIPCODE,COUNT(EMPLOYEE_ID),BRANCHE_NUMBER AS NO_OF_EMP FROM BRANCHES,EMPLOYEES GROUP BY BRANCH ORDER BY NO_OF_EMP,STREET;
4. SELECT STREET,CITY,ZIP_CODE,BRANCH_NUMBER,COUNT(EMPLOYEE_ID) AS NO_OF_EMP FROM BRANCHES,EMPLOYEES WHERE NO_OF_EMP>=3 GROUP BY BRANCHES ORDER BY STREET;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.