SQL Problems
Q1
Lives
| Column Name | Data Type |
|---|---|
| Name | VARCHAR2(255) |
| Street | VARCHAR2(255) |
| City | VARCHAR2(255) |
Works
| Column Name | Data Type |
|---|---|
| Name | VARCHAR2(255) |
| Company_Name | VARCHAR2(255) |
| Salary | NUMBER |
Located_In
| Column Name | Data Type |
|---|---|
| Company_Name | VARCHAR2(255) |
| City | VARCHAR2(255) |
Manager
| Column Name | Data Type |
|---|---|
| Name | VARCHAR2(255) |
| Manager_Name | VARCHAR2(255) |
Queries
-- finding employees with salary greater than AVG
SELECT Name, Salary
FROM Works
WHERE Salary > (SELECT AVG(Salary) FROM Works);
-- selecting columns from <table>
SELECT <col_1>, <col_2>
FROM <table>;
-- WHERE <col_1> > 2000
-- name and city of employess who work for bank of india
SELECT Lives.Name, Lives.City
FROM Lives
JOIN Works ON Lives.Name = Works.Name
WHERE Works.Company_Name = 'Bank of India';
-- selecting employees with manager as 'Alice'
SELECT Works.Name, Works.Salary, Manager.Manager_Name
FROM Works
JOIN Manager ON Works.Name = Manager.Name
WHERE Manager.Manager_Name = 'Alice';
-- selects specific columns from two tables (<table_1> and <table_2>) and combines the results based on a common primary key
SELECT <table_1>.<col_nam>,<table_2>.<col_nam>
FROM <table_1>
JOIN <table_2> ON <table_1>.<prim_id> = <table_2>.<prim_id>
-- update table so Agarwal now lives in shastri nagar
UPDATE Lives
SET City = 'Shastri Nagar'
WHERE Name = 'Agarwal';
-- selecting employee name and company name
SELECT Works.Name, Located_in.Company_Name
FROM Located_in
JOIN Works ON Located_in.Company_Name = Works.Company_Name;
-- no of employees working for each company from same table
SELECT Company_Name,COUNT(*)
FROM Works
GROUP BY Company_Name;
Q2
Employee
| Column | Data Type | Description |
|---|---|---|
| Eid | NUMBER(5) | Employee ID (Primary Key) |
| empname | VARCHAR2(50) | Employee Name |
| street | VARCHAR2(100) | Street Address |
| city | VARCHAR2(50) | City |
Works
| Column | Data Type | Description |
|---|---|---|
| Eid | NUMBER(5) | Employee ID (Foreign Key) |
| Cid | NUMBER(5) | Company ID |
| Salary | NUMBER(10, 2) | Salary |
Company
| Column | Data Type | Description |
|---|---|---|
| Cid | NUMBER(5) | Company ID (Primary Key) |
| Compname | VARCHAR2(50) | Company Name |
| city | VARCHAR2(50) | City |
Queries
-- select employees working in TCS making greater than 30000
SELECT Employee.empname,Employee.street, Employee.city
FROM Employee
JOIN Works ON Employee.Eid = Works.Eid
JOIN Company ON Works.Cid = Company.Cid
WHERE Company.Compname='TCS' AND Works.Salary> 30000;
-- all employees with name starting with S
SELECT empname
FROM Employee
WHERE empname LIKE 'S%';
-- count of all records in Employee table
SELECT COUNT(*) from Employee;
-- finding annual salary of Employees
SELECT Employee.empname, Works.Salary*12
FROM Employee
JOIN Works ON Employee.Eid = Works.Eid;