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:
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”);
Q2. Insert a record into Product table with the values "110, Bat, 50, Sports, NULL" as "ProdId,
PDesc, Price, Category, Discount" respectively.
Ans: INSERT INTO Product Values(110,”Bat”,50,”Sports”,”Null”);
Product Table
Ans: UPDATE Product SET DISCOUNT=25 WHERE CATEGORY='Sports';
Q4. Update the price of products to 50 whose category is 'Apparel' and product description is 'Trouser'.
Ans : UPDATE Product SET PDSEC=50 WHERE CATEGORY=”Apparel” AND PDESC=”Trouser”;
Q5. Update the Salesman's name to 'Jenny' and location to 'Bristol' for the salesman having SID as 3.
Ans : UPDATE SALESMAN SET SNAME=’Jenny’ AND LOCATION =’Bristol’ WHERE SID=3;
Q6. Delete all records from the SaleDetail table for SaleId 1004.
SaleDetail Table
Ans: DELETE FROM SaleDetail WHERE SALEID=1004;
Q7. Joins Display the sale id and sale date of sales made by salesmen working from London.
Ans: SELECT SALESMAN.SALEID, SALESMAN.SLDATE FROM SALESMAN INNER JOIN
SALE ON SALESMAN.SID=SALE.SID
WHERE SALE.LOCATION=’LONDON’;
Q8. 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;
ANS: select ID, ENAME from Employee where SALARY > 30000 and DESIGNATION = 'PM';
Q10. Display ID, ENAME FROM Employee relation if SALARY is in the range 30000 and 50000.