SQL Practice Questions

When we planed to learn Database we can divide the learning in four parts:

1. Knowing the DBMS concepts. 
2. Knowing the syntax and concepts of database technology(MS SQL, Oracle etc).
3. Practicing the SQL.
4. Digging the depth of Database engine/ Administration

#3 Practice SQL is most common in all the SQL based database and also the 80% of Database works is done by expert SQL Query Developer. In this blog I am writing some SQL practice question that will help you to test/improve your SQL Skill.


Below SQL Practice question are based on Adventure Works database, you can download the Adventure Works  database from below Link. Click Here to download the database.


To download the Adventure works Data- model you can Click Here

Ad-hoc Query:

1. Show the transaction history of the red color product.
2. List all the product name with category, subcategory along with inventory location.
3. List out the product with the latest price of the product.
4. List out the Product for that their in not a single order in this month.
5. Show the product with special offer, Only the product having special offer.
6. List out all the product with total sales order for that product.
7. Show the product having max number of order this year.
8. Show the details of a employing having max number of order.
9. Show all the person names with home address and with office phone number.
10. Show the employee those who joined this year.

Stored Procedures :

1. Create a procedure to Insert a new Product information, take the parameter as  Product name, category name ,sub category name, Model name etc.
2. Create a procedure to Insert a new employee information, and assign him a department. Along with employee information also take Department name as parameter.
3. Create a procedure to create order for a product.

Note :  You can keep your answer in comment or if you want the query from my side also you can write in comment.

Writing SQL Server data to fixed length format file

     

      SQL Server provides different ways to write data to file system like BCP, BAT FILE, XP_CMDSHELL, OLE Automation Object. We can write data in file system in different format like delimiter separated, fixed length format here we are explain fixed length file format.

There are several ways to write data from SQL Server to file system using some intermediate tool like SSIS or other ETL tools, but when you need to write data without using any tool then its bit challenge.

Requirement: Write table data to the file in fixed length format for a selected Id.

      

   

     What is a fixed length file?

Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left/right alignment.  Column widths are measured in units of characters. For example, if you have data in a text file where the first column always has exactly 10 characters, and the second column has exactly 5, the third has exactly 12 (and so on); this would be categorized as a fixed width text file.

  I have written a white paper for details process, below is the link:


Click Here :Download White paper

Column level security in SQL Server without using View

We can create column level security in SQL Server without using the view. We can manage the column level security with Database roles. Below are the demo scripts:

 /*Step-1 : create login */

Create login vitest with password ='test#135'

GO

/*Step-2:  Create database user */

Use<Database name >

GO

CREATE user vitest

FOR LOGIN vitest

/* Step-3: Create a role */

CREATE ROLE rolevtest

 /* Step-4: Grant the select permission on that specific column/s  */

GRANT SELECT

 ON OBJECT::testbox.Box(form_id)
 TO rolevtest
 

/*Step-5: Grant above role to the database user*/

EXEC sp_addrolemember 'rolevtest'
 ,'vitest';

 /* Step-6 :Login with newly created user and try below */

SELECT Box_id

FROM  testbox.Box /* it will work */
SELECT Box_id, Box_Name
FROM  testbox.Box /* it will not work */

Query Compilation Process

SQL Server query optimiser is a cost based optimiser, which means that is tried to come up with a low cost execution plan for each SQL statement. Each execution plan has associate cost in term of the amount of computing resource used. The query optimiser analyses the entire possible plan and chooses one of the lowest estimated cost plans. Some time a complex SELECT statement has thousands of possible plans in that case optimiser will not compile the entire possible plan instead it tried to find an execution plan that has a cost reasonably close to the theoretical cost. 

       Parsing process just convert statement into system understandable format, after that Algebrizer validate the statement and make sure that  referenced object , column do exists and the statement is valid to process the data. Then algebrized expression tree will compiled by the query optimiser. After that execution plan generated, it is placed in cache and then execute. 

Tuning Performance of Bulk INSERT / Reduce transaction log file growth by Minimal Logging

  One of my friend asked me that is there any way in SQL Server to minimise the  transaction logging for a table so that INSERT operation run faster? He is expert in ORACLE  Database and In Oracle database have feature of NOLOGGING that you can set for a table.

   Answer is NO. SQL Server don't have any direct keyword as NOLOGGING but we have alternative to reduce the logging.

Requirement : Suppose you have a big table like Fact table where every night you used to load huge data around 100GB, here transact is not much matter as its not a transnational table. Now when you load this much of  data then Transnational Log file grow very fast, also the INSERT operation will run slow.

      We can tune the performance of  the INSERT operation in several ways; here we will discuss one of the way by reducing the logging. To reduce the logging we need to change the database Recovery model to SIMPLE or BULK_LOGGED. Even it is in FULL recovery model, you can use TABLOCK table hints to minimise the logging. Finally you can use below steps to boost the INSERT performance:

1. Set the Database Recovery model to SIMPLE and use the TABLOCK.
2.  Set the Database Recovery model to BULK_LOG and use the TABLOCK.
3.  Set the Database Recovery model to FULL and use the TABLOCK.

          Here option #1 is the best way, but if your database need point of time recovery and you used to take the transaction log backup then this option will not work for it. So you can use the option #3. When you used to dump the data from heterogeneous file you can use the option #2.These will also help to reduce the transaction log file growth:

   Below are the analyses to see the performance difference and Transaction Log file size differences:

   

  DEMO:

   I have created 6 Database as below with different Recovery model :

     

            create database bulktestsimple

alter database bulktestsimple  set recovery simple

create database bulktestsimpleLock

alter database bulktestsimpleLock  set recovery simple

create database bulktestFull

alter database bulktestFull  set recovery Full

create database bulktestFullLock

alter database bulktestFullLock  set recovery Full

create database bulktestbul

alter database bulktestbul set recovery bulk_logged

create database bulktestbulLock

alter database bulktestbulLock set recovery bulk_logged

Let's See the size of Log file of all the databases, here all log file size are 63 MB.

       Now create table in every databases and load the data.

    

1.    . Database with simple recovery mode 

1. use bulktestsimple
   GO
   
   
   --Creating blank table
   select a.* into DumpTable  from AdventureWorks2008.sys.objects a
   cross join AdventureWorks2008.sys.objects b
   cross join sys.tables
   where 1=2
   
   
   
   
   --Loading Data
   
   
   insert into DumpTable
   select a.*   from AdventureWorks2008.sys.objects a
   cross join AdventureWorks2008.sys.objects b
   cross join master.sys.tables

          2.Database is in simple Recovery Mode  and load data with TABLOCK hints:

  

select a.* into DumpTable  from AdventureWorks2008.sys.objects a

cross join AdventureWorks2008.sys.objects b

cross join sys.tables

where 1=2

--Loading Data

insert into DumpTable with (tablock)

select a.*   from AdventureWorks2008.sys.objects a

cross join AdventureWorks2008.sys.objects b

cross join master.sys.tables

     I have did same for all other 4 Databases and then we find below : 

    Log file size after data loading 

    Time  taken by different databases for same set of data load :

1. Simple Recovery mode , without table hints --59 Second

2. Simple Recovery mode , with table hints -- 16  Second

3. Full Recovery mode , Without table hints --1. 4 Minutes

5. Full Recovery mode , With table hints --25 Second

   Conclusion: With the help of TABLOCK we can reduce the Logging and increase the performance, Its only helpful for the Non transaction tables.

Analyze Script Performance- Include Client Statistics

Include client statistic “is one of the helpful and interesting tool of the SQL Server Management Studio for query tuning and to analyze the script performance. It displays information about the query execution grouped into categories; it helps to analyze script performance.

It’s very helpful if you want to know below property of executed scripts:

1.      How much byte of data sent from client?

2.      How much byte of data received from the server?

3.      How many TDS packets sent from the client?

4.      How many TDS packets received from server?

5.      Number of server round trips.

6.      Number of transaction etc.

 Sometime query run slow due to slow network traffic between client and server, or because of high number of server roundtrips. (Creating procedure is one of the solutions for this).

  When we run a script  in the Transact-SQL Editor, we can choose to collect client statistics such as application profile, network, and time statistics for the execution. Such metrics allow us to gauge the efficiency of the script, or benchmark different scripts.

• How to use?

To see the client statistics we need to select “Query” menu and select “Include client statistics” .

 

• How it works?

It records 10 regular executions of that session and shows the statistics together where we can compare the value of different parameters of the Query execution. We can also reset the client statistics.  In client statistic windows it will show green arrow if value is decreased, Red arrow if values increased, and black arrow if value is same from privies trial value.

Try below sample scripts :

Use tempdb


Go

/*Trial-1*/

select * from sys.tables


select * from sys.columns



/*Trial-2*/



select * from sys.tables

GO

select * from sys.columns

/*Create procedure*/


Create Proc mdata


as


begin


set NoCount ON


select * from sys.tables


select * from sys.columns


end

/* Trial- 3*/



EXEC mdata

Finding dependent procedure of every procedure

Finding dependent procedure of every procedure :

ALTER FUNCTION dependency (@name varchar(200))

RETURNS TABLE

AS

  RETURN

  SELECT

    OBJECT_NAME(object_id) objectname

  FROM sys.sql_modules

  WHERE definition LIKE '%' + @name + '%'

  AND OBJECT_NAME(object_id) <> @name

GO

 

SELECT

  sp.name,

  d.objectname AS dependentSP

FROM sys.procedures sp

CROSS APPLY dbo.dependency(sp.name) AS d

ORDER BY 1

GO