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.

Introduction
In this article, we will be looking for a prevalent yet very important topic i.e. SQL also pronounced as Ess-cue-ell. So this time I’ll be answering some of the factual questions about SQL which every beginner needs to know before getting started with SQL database programming. Here we will not only discuss the technicalities of SQL but also some functional knowledge that helps one to know their role and responsibility while working with database design and data analysis.


sql database



What is SQL?

sql database


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)

Write query for the following: 
(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

ANS:
(1) CREATE TABLE new_table AS (SELECT * FROM original_table);
(2) DELETE FROM table_name WHERE row_id NOT IN (SELECT MIN(row_id) FROM table_name GROUP BY column1, column2, ...); 
(3) ALTER TABLE table_name ADD COLUMN new_column data_type; 
(4) SELECT * FROM table_name ORDER BY column_name ASC/DESC;


1.Insert a record into Salesman table with the values "11, Elizabeth, London" as "SId, SName, Location" respectively.

Salesman Table


SID

SNAME

LOCATION

1

Peter

London

2

Michael

Paris

3

John

Mumbai

4

Harry

Chicago

5

Kevin

London

6

Alex

Chicago

 

Ans :INSERT INTO Salesman VALUES(11,”Elizabeth”,”London”);


 

  1. Insert a record into Product table with the values "110, Bat, 50, Sports, NULL" as "ProdId, PDesc, Price, Category, Discount" respectively.


Product Table


      

PRODID

PDESC

PRICE

CATEGORY

DISCOUNT

101

Basketball

10

Sports

5

102

Shirt

20

Apparel

10

103

NULL

30

Electronics

15

104

Cricket Bat

20

Sports

20

105

Trouser

10

Apparel

5

106

Television

40

ELECTRONICS

20

 

Ans : INSERT INTO Product Values(110,”Bat”,50,”Sports”,”Null”);

 


  1. Update discount of all products whose category is 'Sports' to 25 percent.


Product Table


PRODID

PDESC

PRICE

CATEGORY

DISCOUNT

101

Basketball

10

Sports

5

102

Shirt

20

Apparel

10

103

NULL

30

Electronics

15

104

Cricket Bat

20

Sports

20

105

Trouser

10

Apparel

5

106

Television

40

ELECTRONICS

20


Ans: UPDATE Product SET DISCOUNT=25 WHERE CATEGORY='Sports';


  1. Update price of products to 50 whose category is 'Apparel' and product description is 'Trouser'.


Product Table


PRODID

PDESC

PRICE

CATEGORY

DISCOUNT

101

Basketball

10

Sports

5

102

Shirt

20

Apparel

10

103

NULL

30

Electronics

15

104

Cricket Bat

20

Sports

20

105

Trouser

10

Apparel

5

106

Television

40

ELECTRONICS

20


Ans : UPDATE Product SET PDSEC=50 WHERE CATEGORY=”Apparel” AND PDESC=”Trouser”;



  1. Update the Salesman name to 'Jenny' and location to 'Bristol' for the salesman having SID as 3.


Salesman Table


SID

SNAME

LOCATION

1

Peter

London

2

Michael

Paris

3

John

Mumbai

4

Harry

Chicago

5

Kevin

London

6

Alex

Chicago


Ans : UPDATE SALESMAN SET SNAME=’Jenny’ AND LOCATION =’Bristol’ WHERE SID=3;



  1. Delete all records from SaleDetail table for SaleId 1004.


SaleDetail Table


SALEID

PRODID

QUANTITY

1001

103

1

1001

106

2

1002

101

1

1002

102

5

1003

101

1

1003

104

1


Ans : DELETE FROM SaleDetail WHERE SALEID=1004;

 

7. Joins Display the sale id and sale date of sales made by salesmen working from London.


SALEID

SID

SLDATE

1001

1

01-JAN-14

1002

5

02-JAN-14

1003

4

01-FEB-14

1004

1

01-MAR-14

1005

2

01-FEB-14

1006

1

01-JUN-15

Salesman Table


Sale Table

SID

SNAME

LOCATION

1

Peter

London

2

Michael

Paris

3

John

Mumbai

4

Harry

Chicago

5

Kevin

London

6

Alex

Chicago


Expected Result

SALEID

SLDATE

1001

01-JAN-14

1006

01-JUN-15

1004

01-MAR-14

1002

02-JAN-14


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


ID

ENAME

DEPT

MANAGER

COMPID

1

James Potter

ICP

NULL

1001

2

Ethan McCarty

ETA

NULL

NULL

3

Emily Rayner

ETA

2

1002

4

Jack Abraham

ETA

2

NULL

5

Ayaz Mohammad

ICP

1

1003

COMPID

MODEL

1001

Vostro

1002

Precision

1003

Edge

1004

Horizon


Expected Result

ENAME

DEPT

MODEL

James Potter

ICP

Vostro

Ethan McCarty

ETA

Not allocated

Emily Rayner

ETA

Precision

Jack Abraham

ETA

Not allocated

Ayaz Mohammad

ICP

Edge


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


ID

ENAME

DEPT

MANAGER

COMPID

1

James Potter

ICP

NULL

1001

2

Ethan McCarty

ETA

NULL

NULL

3

Emily Rayner

ETA

2

1002

4

Jack Abraham

ETA

2

NULL

5

Ayaz Mohammad

ICP

1

1003

COMPID

MODEL

1001

Vostro

1002

Precision

1003

Edge

1004

Horizon

Expected Result

ID

ENAME

DEPT

MODEL

1

James Potter

ICP

Vostro

2

Ethan McCarty

ETA

NULL

3

Emily Rayner

ETA

Precision

4

Jack Abraham

ETA

NULL

5

Ayaz Mohammad

ICP

Edge

NULL

NULL

NULL

Horizon


ANS: SELECT Employee.ID,Employee.ENAME,Employee.DEPT,Computer.Model FROM Employee FULL OUTER JOIN Computer ON Employee.COMPID=Computer.COMPID;


  1. 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; 


  1. 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'; 

  1. 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;


  1. 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');


  1. Exercise 57: JOINS

Display the saleman id and names of salesmen who have not made any sales.

Salesman Table Sale Table

SALEID

SID

SLDATE

1001

1

01-JAN-14

1002

5

02-JAN-14

1003

4

01-FEB-14

1004

1

01-MAR-14

1005

2

01-FEB-14

1006

1

01-JUN-15


SID

SNAME

LOCATION

1

Peter

London

2

Michael

Paris

3

John

Mumbai

4

Harry

Chicago

5

Kevin

London

6

Alex

Chicago

Expected Result

SID

SNAME

3

John

6

Alex


Ans: SELECT Sale.SID,Sale.SNAME FROM Salesman RIGHT JOIN Sale ON Salesman.SID=Sale.SID WHERE Salesman.SID IS NULL;