SQL

SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with database. e.g. SQL Developer, Oracle DB

SQL Developer:

Oracle SQL Developer (internally often: “sqldeveloper”) is an Integrated development environment (IDE) for working with SQL in Oracle databases.


However, there is also a database management system and the ‘SQL’ word is integrated in to the title of that database. Which is called Microsoft SQL Server. Microsoft SQL Server is a relational database management system developed by Microsoft. is a relational database management system developed by Microsoft.


Followings Are Few Common SQL Statements.


JOINS


Inner Join :
 select * from tblCountry

inner join tblState

where tblCountry.CountryId=tblState.CountryId

Left Join: select * from tblCountry

left join tblState

where tblCountry.CountryId=tblState.CountryId

Right Join: select * from tblCountry

right join tblState

where tblCountry.CountryId=tblState.CountryId

Full Outer Join: select * from tblCountry

full outer tblState

where tblCountry.CountryId=tblState.CountryId

 


CASE:

CASE is used to provide if-then-else type of logic to SQL. There are two formats: The first is a Simple CASE expression, where we compare an expression to static values. The second is a Searched CASE expression, where we compare an expression to one or more logical conditions.

 

Example 1:

select ContactName, case  country

when ‘Germany’ then ‘German’

when ‘Mexico’ then ‘Mexican’

else ‘Not Found’

end as Nationality

from customers

order by Nationality

 

Example 2:

select ProductID,

case

when UnitPrice<20 then “Cheap”

when UnitPrice<80 then “Moderate”

else “Wow price”

end as Expensiveness

from Products

order by Expensiveness

Example 3:

Select filmName

,FilmRunTimeMinutes

,case

whenFilmRunTimeMiniutes <=90 then “short”

whenFilmRunTimeMiniutes <=150 then “medium”

whenFilmRunTimeMiniutes <=180 then “llong”

else ‘Epic’

end as [FilmDuration]

,FilmReleaseDate

from tblFilm

where

case

whenFilmRunTimeMiniutes <=90 then ‘short’

whenFilmRunTimeMiniutes <=150 then ‘medium’

whenFilmRunTimeMiniutes <=180 then ‘long’

else ‘Epic’

end= ‘medium’

 

Example 4:

Select filmName

,case

When FilmName like ‘%twillight%’ then ‘Just Awful’

else ‘Not Bad’

end

from tblFilm


DELETE Vs TRUNCATE

DML Vs DML

ROLEBACK Vs No ROLLBACK

Takes ‘where’ for selected rows Vs Deletes the empty the table. Does not take ‘where’ command

Example:

delete from Employee

Where DeptNo=10;

 

Other popular SQL Statements: COMMIT, UPDATE, ROLLBACK


INTERVIEW QUESTIONS and ANSWERS


1. What are Sub-queries in SQL ?


2. What is Partitioning in SQL?

Example Table

Partition-eample table-

2.a. RowNumber (Numbering Rows)

SELECT rownumber() over (order by customername) as ordernumber,

Customername,
Productname,
Amount,
Vendorname
FROM Sales

Result

rownumber-partition-result

2.b. Partition (Partitioning and re-numbering based on partition)

SELECT rownumber() over (order by customername) as ordernumber,

rownumber() over (partition by vendorname order by vendorname) as vendorordernumber,

Customername,

Productname,

Amount,

Vendorname

FROM Sales

Result

Partition-eample table-

2.c. RANK/DENSE_RANK (Generating unique number for customer. However, I want same number for same customer)

SELECT rownumber() over (order by customername) as ordernumber,

rownumber() over (partition by vendorname order by vendorname) as vendorordernumber,

dense_ rank() over (order by customername) as customernumber,

Customername,

Productname,

Amount,

Vendorname

FROM Sales

Result

Dense -Rank-Partition-result

[https://www.youtube.com/watch?v=QFj-hZi8MKk]

1,168 total views, 2 views today