SQL Query Practice #2

List the names of all customers who have excellent credit ratings and are in the zip code 620015.

List the name, credit rating and sales representatives of the customers who either do not use the sales representative 12 whatever their credit rating or do not have an excellent credit rating whoever their sales representative is.

List the name, address and city of any customers whose names begin with words like Christina or Christopher. List them alphabetically.

List the id, name and phone number of any customers whose name begins with letters of the alphabet between A and S.

List last name, first name, title and manager ID of all employees whose manager IDs are 1, 2 or null. Order the results alphabetically by title, last name, and first name.

Display all the employee’s salaries rounded to the nearest 100

Display the last name (right-justified), the first initial of the first name for all the stock clerks.

Print the id, first name and last name of the employees with odd-numbered ids. (Convert char type id to number).

Show for each order how many days elapsed between order date and shipping date.

Add a discount Rs.500 for any order total over 7000. Calculate the total, including 5% sales tax for those orders.

Print the total, total truncated to the nearest 100, and the absolute value of how much each orders truncated total varies from 7000.

Display employees first and last name with initial caps, separated by a comma in a single column.

Display all the employees last name where the name has all S’s replaced with a dollar sign.

Display all the employees first name that has the same number of letters in last name

Challenge

Use the tables given in the SQL Query Practice #1

Express the following queries in SQL:

List the names of all customers who have excellent credit ratings and are in the zip code 620015.
List the name, credit rating and sales representatives of the customers who either do not use the sales representative 12 whatever their credit rating or do not have an excellent credit rating whoever their sales representative is.
List the name, address and city of any customers whose names begin with words like Christina or Christopher. List them alphabetically.
List the id, name and phone number of any customers whose name begins with letters of the alphabet between A and S.
List last name, first name, title and manager ID of all employees whose manager IDs are 1, 2 or null. Order the results alphabetically by title, last name, and first name.
Display all the employee’s salaries rounded to the nearest 100.
Display the last name (right-justified), the first initial of the first name for all the stock clerks.
Print the id, first name and last name of the employees with odd-numbered ids. (Convert char type id to number).
Show for each order how many days elapsed between order date and shipping date.
Add a discount Rs.500 for any order totalling over 7000. Calculate the total, including 5% sales tax for those orders.
Print the total, total truncated to the nearest 100, and the absolute value of how much each orders truncated total varies from 7000.
Display employees first and last name with initial caps, separated by a comma in a single column.
Display all the employees last name where the name has all S’s replaced with a dollar sign.
Display all the employees first name that has the same number of letters in last name

Solutions

List the names of all customers who have excellent credit ratings and are in the zip code 620015.

select * from customer where credit_rating > 8 and zipcode = 620015;

List the name, credit rating and sales representatives of the customers who either do not use the sales representative 12 whatever their credit rating or do not have an excellent credit rating whoever their sales representative is.

SELECT customer_name, credit_rating, sales_rep_id FROM customer WHERE sales_rep_id <> 12 OR credit_rating < 8;

List the name, address and city of any customers whose names begin with words like Christina or Christopher. List them alphabetically.

SELECT customer_name, state, city FROM customer WHERE customer_name LIKE '%Christina%' OR customer_name LIKE '%Christopher%' ORDER BY customer_name;

List the id, name and phone number of any customers whose name begins with letters of the alphabet between A and S.

SELECT customer_id, customer_name, zipcode FROM customer WHERE upper(customer_name) BETWEEN 'A' AND 'S';

List last name, first name, title and manager ID of all employees whose manager IDs are 1, 2 or null. Order the results alphabetically by title, last name, and first name.

SELECT employee_last_name, employee_first_name, title, manager_id FROM employee WHERE manager_id=1

 OR manager_id=2 OR manager_id IS NULL ORDER BY title, employee_last_name, employee_first_name;

Display all the employee’s salaries rounded to the nearest 100

SELECT employee_last_name, ROUND(salary) AS rounded FROM employee;

Display the last name (right-justified), the first initial of the first name for all the stock clerks.

SELECT rpad(employee_last_name, 12, ' ') AS rname, substr(employee_first_name, 1, 1) AS lname from

 employee WHERE title = 'Stock Clerk';

Print the id, first name and last name of the employees with odd-numbered ids. (Convert char type id to number).

SELECT employee_id, employee_first_name, employee_last_name FROM employee WHERE mod(employee_id,2)=1;

Show for each order how many days elapsed between order date and shipping date.

SELECT order_id, (date_shipped - date_ordered) FROM orders;

Add a discount Rs.500 for any order total over 7000. Calculate the total, including 5% sales tax for those orders.

UPDATE orders SET total = (total-500) + (total*0.05) WHERE total > 7000;

SELECT order_id, total FROM orders;

Print the total, total truncated to the nearest 100, and the absolute value of how much each orders truncated total varies from 7000.

SELECT total, round(total,-2), abs(7000 - round(total,-2)) FROM orders;

Display employees first and last name with initial caps, separated by a comma in a single column.

SELECT (initcap(employee_first_name))||','||(initcap(employee_last_name)) as name FROM employee;

Display all the employees last name where the name has all S’s replaced with a dollar sign.

SELECT REPLACE(employee_last_name, 's','$') from employee;

Display all the employees first name that has the same number of letters in last name

SELECT employee_first_name, employee_last_name FROM employee WHERE length(employee_first_name) = length(employee_last_name);

Page updated

Google Sites

Report abuse