Introduction
What is SQL?
SQL stands for Structured Query language, whenever we used hear "SQL" the straightaway first thing that comes to our mind is – designing the database SQL is not only about designing and creating a database but it can be a very important tool in the case of drawing insights from the database itself, SQL sometimes turns out to be an underrated skill when it comes to making business-based decisions but it is one of the important tools to make some reliable and efficient decisions.
Some basic database operations that SQL can perform: Creating a database
Adding new data to the existing database
Updating the existing data/schema
Segmenting the data (Group By clause with aggregate function)
Ordering the data based on the requirements (Order By clause)
(1) To create a table from a table.
(2) To eliminate duplicate rows.
(3) To add a new column in the table
(4) To sort data in a table
(1) CREATE TABLE new_table AS (SELECT * FROM original_table);
Salesman Table
Ans :INSERT INTO Salesman VALUES(11,”Elizabeth”,”London”);
Insert a record into Product table with the values "110, Bat, 50, Sports, NULL" as "ProdId, PDesc, Price, Category, Discount" respectively.
Product Table
Ans : INSERT INTO Product Values(110,”Bat”,50,”Sports”,”Null”);
Update discount of all products whose category is 'Sports' to 25 percent.
Product Table
Ans: UPDATE Product SET DISCOUNT=25 WHERE CATEGORY='Sports';
Update price of products to 50 whose category is 'Apparel' and product description is 'Trouser'.
Product Table
Ans : UPDATE Product SET PDSEC=50 WHERE CATEGORY=”Apparel” AND PDESC=”Trouser”;
Update the Salesman name to 'Jenny' and location to 'Bristol' for the salesman having SID as 3.
Salesman Table
Ans : UPDATE SALESMAN SET SNAME=’Jenny’ AND LOCATION =’Bristol’ WHERE SID=3;
Delete all records from SaleDetail table for SaleId 1004.
SaleDetail Table
Ans : DELETE FROM SaleDetail WHERE SALEID=1004;
7. Joins Display the sale id and sale date of sales made by salesmen working from London.
Salesman Table
Sale Table
Expected Result
Ans: SELECT SALESMAN.SALEID,SALESMAN.SLDATE FROM SALESMAN INNER JOIN SALE ON SALESMAN.SID=SALE.SID WHERE SALE.LOCATION=’LONDON’;
8. JOINS Display name and department of employees along with the model of the computer allocated to them. Employee details must be displayed with ‘Not allocated’ against the model column if computer is not allocated to the person.
Employee Table
Computer Table
Expected Result
Ans: SELECT Employee.ENAME,Employee.DEPT,IF(Computer.Model IS NULL,’Not Allocated’,Computer.Model) FROM Employee RIGHT JOIN Computer ON Employee.COMPID=Computer.COMPID;
9. Exercise 55: JOINS
Display id, name, department of employees along with model of computer allocated to them. The details for employee or computer should be displayed even if the employee has not been allocated a computer or a computer is not assigned to an employee.
Employee Table Computer Table
Expected Result
ANS: SELECT Employee.ID,Employee.ENAME,Employee.DEPT,Computer.Model FROM Employee FULL OUTER JOIN Computer ON Employee.COMPID=Computer.COMPID;
Write a query to list product id, price and category for all products from the Product table. Display product id in descending order.
ANS:
select PRODID,PRICE,CATEGORY from Product order by PRODID desc;
Display ID, ENAME FROM Employee relation. The condition is SALARY greater than 30000 and DESIGNATION is PM.
ANS: select ID, ENAME from Employee where SALARY > 30000 and DESIGNATION =
'PM';
Display ID, ENAME FROM Employee relation if SALARY is in the range 30000 and 50000.
ANS: select ID , ENAME from Employee where SALARY >= 30000 and SALARY <= 50000;
Display ID, ENAME FROM Employee relation. ID should not be 2 or 3.
ANS: select ID, ENAME from Employee where ID not in ('2','3');
Exercise 57: JOINS
Display the saleman id and names of salesmen who have not made any sales.
Salesman Table Sale Table
Expected Result
Ans: SELECT Sale.SID,Sale.SNAME FROM Salesman RIGHT JOIN Sale ON Salesman.SID=Sale.SID WHERE Salesman.SID IS NULL;
0 Comments