Embedded Files
Challenge
Challenge
Consider the following tables:
customer (customer_id, customer_name, city, state, country, zipcode, credit_rating, sales_rep_id, region_id)department (department_id, department_name, region_id)employee (employee_id, employee_last_name, employee_first_name, start_date, manager_id, title, department_id, salary, commission_pct)region (region_id, region_name)order (order_id, customer_id, date_ordered, date_shipped, sales_rep_id, total, payment_type, order_filled)Express the following queries in SQL:
- Display the first name and salary of all employees whose salary is less than 10000.
- Increase the salary of all employees of department 41 by 10%.
- Change the country as India whose customer id is 12438.
- Display the last name, first name and the department id for all employees who do not work in department 44.
- Display the last name, first name and salary of all employees who are stock clerks. Order the result in ascending order by salary.
- Display the last name and hire date of all employees who were hired after august 31, 2008.
- Add the constraint check to the salary (not less than 500) column of Emp table.
- Add NOT NULL constraint to the region name.
- Create a separate table with the same structure as the Order table to hold the archive records. Using the insert statement, copy the records from the Order table to the archive table relating to order before 1 January 1990. Delete all order before 1 January 1990 from the Order table.
- For every employee, display the employee’s name and the name of his or her manager.
- List all the employees who do not have a manager
- Add unique constraint to the employee name
- Drop the added unique constraints
- Display the name and the region in which they are located for all departments
- Display the first and last name of all sales representatives and their customers
- Display all customers and their orders. Make sure that the customers that have not placed an order are also displayed.
Create Tables
Create Tables
CREATE TABLE region ( region_id int primary key, region_name varchar(20) );CREATE TABLE department( department_id int primary key, department_name varchar(20), region_id int, foreign key (region_id) references region(region_id) );CREATE TABLE employee ( employee_id int primary key, employee_last_name varchar(20), employee_first_name varchar(20), start_date date, manger_id int, title varchar(20), department_id int, foreign key (department_id) references department(department_id), salary number(10,2), commissionpct number(10,2) );CREATE TABLE customer ( customer_id int primary key, customer_name varchar(20), city varchar(20), state varchar(30), country varchar(30), zipcode number(6,0), credit_rating number(2,1), sales_rep_id int, foreign key (sales_rep_id) references employee(employee_id), regionid int, foreign key (regionid) references region(region_id) );CREATE TABLE order ( order_id int primary key, customer_id int, foreign key (customer_id) references customer(customer_id), date_ordered date, date_shipped date, sales_rep_id int, foreign key (sales_rep_id) references employee(employee_id), total number(10,2), payment_type varchar(20), order_filled number(1) );Insert Data
Insert Data
INSERT INTO region VALUES(1,'NORTH');INSERT INTO region VALUES(2,'SOUTH');INSERT INTO region VALUES(3,'EAST');INSERT INTO region VALUES(4,'WEST');INSERT INTO region VALUES(5,'CENTRAL');INSERT INTO department VALUES(41,'SALES',1);INSERT INTO department VALUES(42,'HR',1);INSERT INTO department VALUES(43,'TECHNICAL',1);INSERT INTO department VALUES(44,'SALES',2);INSERT INTO department VALUES(45,'HR',2);INSERT INTO department VALUES(46,'TECHNICAL',2);INSERT INTO department VALUES(47,'SALES',3);INSERT INTO department VALUES(48,'HR',3);INSERT INTO department VALUES(49,'TECHNICAL',3);INSERT INTO department VALUES(50,'SALES',4);INSERT INTO department VALUES(51,'HR',4);INSERT INTO department VALUES(52,'TECHNICAL',4);INSERT INTO employee VALUES(13, 'Dhankar', 'Abhilash',DATE '2015_10_1', 123, 'Executive Manager', 41, 85000, 5000);INSERT INTO employee VALUES(12438, 'Kumar', 'Ajay',DATE '2013_12_13', 456, 'Sales Manager', 42, 90000, 5000);INSERT INTO employee VALUES(12437, 'Cena', 'John',DATE '1990_02_28', 12, 'HR Manager', 43, 185000, 25000);INSERT INTO employee VALUES(1111, 'Lesnar', 'Brock',DATE '2015_3_23', 123, 'Stock Clerk', 44, 800, 100);INSERT INTO employee VALUES(16756, 'Orton', 'Randy',DATE '2015_05_27', 7123, 'Executive Manager', 45, 9500, 500);INSERT INTO employee VALUES(7654, 'Collaway', 'Mark',DATE '1996_11_03', 12453, 'Stock Clerk', 52, 1000, 50);INSERT INTO employee VALUES(134, 'Roshan', 'Hrithik',DATE '2003_01_10', 1453, 'Sales Manager', 50, 85000, 5000);INSERT INTO customer VALUES(1,'Deepanshu','New Delhi','Delhi','India',110012,8.3,1111,1);INSERT INTO customer VALUES(536,'Shahrukh','Mumbai','Maharashtra','India',400052,6.4,134,2);INSERT INTO customer VALUES(647,'Jeff','Chennai','Tamil Nadu','India',620015,9.1, 12438,4);INSERT INTO customer VALUES(12,'Nickhlous','Kolkata','West Bengal','India',320012,5.3,12437,3);INSERT INTO customer VALUES(12438,'Stephan','Ghaziabad','Uttar Pradesh','China',201012,9.3,16756,1);INSERT INTO order VALUES(1, 12,DATE '2014_04_10',DATE '2014_04_12', 134, 3600, 'Cash', 1);INSERT INTO order VALUES(2, 536,DATE '2014_06_13',DATE '2014_06_16', 7654, 4500, 'Cash', 1);INSERT INTO order VALUES(3, 647,DATE '2014_07_14',DATE '2014_08_01', 1111, 12400, 'Cheque', 1);INSERT INTO order VALUES(4, 12,DATE '2014_08_23',DATE '2014_08_25', 134, 36040, 'Cheque', 0);INSERT INTO order VALUES(5, 536,DATE '2014_09_17',DATE '2014_09_26', 16756, 364500, 'Cash', 0);Solutions
Solutions
Display the first name and salary of all employees whose salary is less than 10000.
Display the first name and salary of all employees whose salary is less than 10000.
SELECT first_name, salary FROM employee WHERE salary < 10000;Increase the salary of all employees of department 41 by 10%.
Increase the salary of all employees of department 41 by 10%.
UPDATE employee SET salary = salary*1.1 WHERE department_id = 41;Change the country as India whose customer id is 12438
Change the country as India whose customer id is 12438
UPDATE customer SET country = 'India' WHERE customer_id = 12438;Display the last name, first name and the department id for all employees who do not work in department 44
Display the last name, first name and the department id for all employees who do not work in department 44
SELECT employee_last_name, employee_first_name, department_id FROM employee WHERE department_id <> 44Display the last name, first name and salary of all employees who are stock clerks. Order the result in ascending order by salary.
Display the last name, first name and salary of all employees who are stock clerks. Order the result in ascending order by salary.
SELECT employee_last_name, employee_first_name, salary FROM employee WHERE title='Stock Clerk' ORDER BY salary;Display the last name and hire date of all employees who were hired after august 31, 2008.
Display the last name and hire date of all employees who were hired after august 31, 2008.
SELECT employee_last_name, start_date FROM employee WHERE start_date > '31_aug_2008';Add the constraint check to the salary (not less than 500) column of employee table.
Add the constraint check to the salary (not less than 500) column of employee table.
ALTER TABLE employee MODIFY (salary number(10,2) CHECK(salary>=500));Add NOT NULL constraint to the region_name.
Add NOT NULL constraint to the region_name.
ALTER TABLE region MODIFY (name varchar(20) not null);Create a separate table with the same structure as the Order table to hold the archive records. Using the insert statement, copy the records from the Order table to the archive table relating to order before 1 January 1990. Delete all order before 1 January 1990 from the Order table.
Create a separate table with the same structure as the Order table to hold the archive records. Using the insert statement, copy the records from the Order table to the archive table relating to order before 1 January 1990. Delete all order before 1 January 1990 from the Order table.
CREATE TABLE new_orders AS SELECT * FROM order WHERE date_ordered < '01_jan_1990';insert into new_orders select * from order where date_ordered<DATE '2014_08_25';For every employee, display the employee’s name and the name of his or her manager.
For every employee, display the employee’s name and the name of his or her manager.
SELECT e.employee_first_name, m.employee_first_name FROM employee eJOIN employee mON e.manager_id = e.employee_id WHERE e.manager_id IS NOT NULL;List all the employees who do not have a manager
List all the employees who do not have a manager
SELECT employee_first_name FROM employee WHERE manager_id is null;Add unique constraint to the employee name
Add unique constraint to the employee name
ALTER TABLE employee add UNIQUE(employee_first_name);Drop the added unique constraint.
Drop the added unique constraint.
ALTER TABLE employee DROP UNIQUE( employee_first_name );Display the name and the region in which they are located for all departments
Display the name and the region in which they are located for all departments
SELECT department.name, region.name FROM department,region WHERE department.region_id = region.region_id;Display the first and last name of all sales representatives and their customers
Display the first and last name of all sales representatives and their customers
SELECT employee_first_name, employee_last_name, customer_name FROM employee, customer WHERE employee.employee_id = customer.sales_rep_id;Display all customers and their orders. Make sure that the customers that have not placed an order are also displayed.
Display all customers and their orders. Make sure that the customers that have not placed an order are also displayed.
SELECT customer_name, order_filled FROM customer,order WHERE customer.customer_id = order.customer_id;Page updated
Google Sites
Report abuse