Microsoft SQL Server: Select Clause SQL Server Tutorial and Interview Questions

Thursday, 12 March 2015

Select Clause SQL Server Tutorial and Interview Questions

Select Clause:-

Specifies the columns to be returned by the query. It's a Data Manipulation Language (DML Statement).

Syntax for Select Clause:-

SELECT [ ALL | DISTINCT ]
[ TOP (expression) [ PERCENT ] [ WITH TIES ] ]
<select_list>

FROM table_name;

Arguments:-

ALL

Specifies that duplicate rows can appear in the result set. ALL is the default.

DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

TOP (expression ) [ PERCENT ] [ WITH TIES ]
Indicates that only a specified first set or percent of rows will be returned from the query result set. expression can be either a number or a percent of the rows.

< select_list >

The columns to be selected for the result set. The select list is a series of expressions separated by commas. The maximum number of expressions that can be specified in the select list is 4096.

Important points about SELECT Clause in SQL Server :-

1). Most often we use SELECT Operator with WHERE Clause, try to use column which has index on WHERE clause. Using a non index column on WHERE clause can slow your query drastically and effect would be more visible when your table data increases.

A example with 1 Million records query without index was taking 80 second while after index it just took .3 second, whopping 260% increase in speed.

2). If you don't need all columns, don’t use * wild card. SELECT query with few columns are slightly faster than all columns.

3). If you are retrieving data from large table, do a count (*) check before firing actual select query, this will give you en estimate of how many records you are about to get and how much time it could take.

4). You can introduce new columns in result set of SELECT Query by using keyword "AS" as shown in below example. Very useful for displaying calculated value e.g. average or percentage.

5). Always use IS NULL or NULL for including or excluding values which could be null. Don’t use 'null' that will be treated as text.

6). While writing SQL query not just SELECT, its good practice to write keyword in small case and TABLES and COLUMNS in capital. So that they will stand out from whole query and makes query more readable.

SQL SELECT Statement Questions :-

1. Identify the capabilities of SELECT statement.

A. Projection

B. Selection

C. Data Control

D. Transaction

Answer: A, B. The SELECT statement can be used for selection, projection and joining.

2. Determine the capability of the SELECT statement demonstrated in the given query.

SELECT e.ename, d.dname

FROM emp e, dept d

WHERE e.deptno = d.deptno

AND e.sal > 1000;

A. Selection

B. Filtering

C. Joining

D. Projection

Answer: A, C, D. Projection is including only the required columns in query, while Selection is selecting only the required data. Joining means combining two tables together through a connecting column.

3. Which of the following clause is used to suppress duplicates in a SELECT statement?

A. INTERSECT

B. DUPLICATE

C. DISTINCT

D. UNIQUE

Answer: C, D. Duplicate data can be restricted with the use of DISTINCT or UNIQUE in the SELECT statement.

4. Finding how many rows in tables.

select count(*) from STOCK;

+----------+

| count(*) |

+----------+

| 5 |

+----------+

5. Finding all records from tables; we are using wildcard start * for getting all columns.

select * from STOCK;

+---------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+---------+-------------------------+--------------------+

| 6758.T | Sony | T |

| GOOG.O | Google Inc | O |

| GS.N | Goldman Sachs Group Inc | N |

| INFY.BO | InfoSys | BO |

| VOD.L | Vodafone Group PLC | L |

+---------+-------------------------+--------------------+

6. Selecting few records based on some condition from tables in SQL Server.

select * from STOCK where RIC='GOOG.O';

+--------+------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+--------+------------+--------------------+

| GOOG.O | Google Inc | O |

+--------+------------+--------------------+

7. How to select few columns instead of all columns?

(Instead of using start wild-card just give name of interested columns to SELECT clause).

select COMPANY from STOCK where RIC='GOOG.O';

+------------+

| COMPANY |

+------------+

| Google Inc |

+------------+

8. Select distinct (unique) records from Columns

(Distinct keyword is used to show only unique records it will not show any duplicate values.)

select distinct LISTED_ON_EXCHANGE from Stock;

+--------------------+

| LISTED_ON_EXCHANGE |

+--------------------+

| T |

| O |

| N |

| BO |

| L |

+--------------------+

9. Selecting value with condition based on less than, greater than (>, <, >=, <=) etc.

select * from Stock where RIC > 'I';

+---------+--------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+---------+--------------------+--------------------+

| INFY.BO | InfoSys | BO |

| VOD.L | Vodafone Group PLC | L |

+---------+--------------------+--------------------+

10. Combining condition using logical operator AND & OR

(AND and OR Can be effectively used to combine two conditions on WHERE clause and gives you lot of flexibility to write SQL query.)

Ans: select * from Stock where RIC <'I' AND RIC > 'G';

+--------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+--------+-------------------------+--------------------+

| GOOG.O | Google Inc | O |

| GS.N | Goldman Sachs Group Inc | N |

+--------+-------------------------+--------------------+

You can put any number of AND, OR conditions on WHERE Clause, some time things become quite easy when you combine AND, OR in SQL.

11. How to find records which is not null using keyword NULL and IS NULL.

NULL is very tricky in SQL; NULL means anything which doesn't have value. NULL is not "null" which will be treated as text.To demonstrate this we will insert a Stock which is not listed on any Market yet.

Ans: select * from STOCK;

+---------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+---------+-------------------------+--------------------+

| 6758.T | Sony | T |

| GOOG.O | Google Inc | O |

| GS.N | Goldman Sachs Group Inc | N |

| INDIGO | INDIGO Airlines | NULL |

| INFY.BO | InfoSys | BO |

| VOD.L | Vodafone Group PLC | L |

+---------+-------------------------+--------------------+

You See there is only one row who has LISTED_ON_EXCHANGE null, we will now see count using NULL and IS NULL which will verify this result.

select count(*) from STOCK where LISTED_ON_EXCHANGE IS NULL;

+----------+

| count(*) |

+----------+

| 1 |

+----------+

select count(*) from STOCK where LISTED_ON_EXCHANGE IS NOT NULL;

+----------+

| count(*) |

+----------+

| 5 |

+----------+

select count(*) from STOCK;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

12. SELECT Statement using BETWEEN and NOT BETWEEN

As name suggest BETWEEN is used to get data between a ranges.

select * from Stock where RIC BETWEEN 'G' AND 'I';

+--------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+--------+-------------------------+--------------------+

| GOOG.O | Google Inc | O |

| GS.N | Goldman Sachs Group Inc | N |

+--------+-------------------------+--------------------+

13. Pattern matching in SQL queries using LIKE and NOT LIKE

LIKE is a pattern matching operator and used to find records which are not exact match but probable match.

select * from Stock where RIC LIKE 'V%';

+-------+--------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+-------+--------------------+--------------------+

| VOD.L | Vodafone Group PLC | L |

+-------+--------------------+--------------------+

NOT LIKE is opposit of LIKE and display records which are not probable match.

select * from Stock where RIC NOT LIKE 'V%';

+---------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+---------+-------------------------+--------------------+

| 6758.T | Sony | T |

| GOOG.O | Google Inc | O |

| GS.N | Goldman Sachs Group Inc | N |

| INDIGO | INDIGO Airlines | NULL |

| INFY.BO | InfoSys | BO |

+---------+-------------------------+--------------------+

14. IN and NOT IN

IN is another useful SQL operator we can use alongside SELECT. it provides set of values which can be used in WHERE cluase.

select * from Stock where RIC in ('GS.N' , 'INFY.BO');

+---------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+---------+-------------------------+--------------------+

| GS.N | Goldman Sachs Group Inc | N |

| INFY.BO | InfoSys | BO |

+---------+-------------------------+--------------------+

15. Sorting ResultSet in SQL using ORDER BY, ASC, DESC

Order by is used to sort records in result set returned by SELECT clause. By default it list in Ascending order but we can use either ascending or descending using specifier ASC and DESC.

select * from Stock order by COMPANY;

+---------+-------------------------+--------------------+

| RIC | COMPANY | LISTED_ON_EXCHANGE |

+---------+-------------------------+--------------------+

| GS.N | Goldman Sachs Group Inc | N |

| GOOG.O | Google Inc | O |

| INDIGO | INDIGO Airlines | NULL |

| INFY.BO | InfoSys | BO |

| 6758.T | Sony | T |

| VOD.L | Vodafone Group PLC | L |

+---------+-------------------------+--------------------+

16. Selecting data from multiple tables by using JOIN in SQL

Join in SQL is powerful concept which allows you to select data from multiple tables. You can generate report where data is accumulated from different tables based on conditions specified in Join statement.

Suppose you need to “display list of Records and Name of Market where they are listed”. Here name of Stock in STOCK table while name of exchange in MARKET table. We need to join both of them to display this report.

select s.RIC, m.NAME from Stock s, Market m where s.LISTED_ON_EXCHANGE=m.RIC;

+---------+-------------------------+

| RIC | NAME |

+---------+-------------------------+

| 6758.T | Tokyo Stock Exchange |

| GOOG.O | NASDAQ |

| GS.N | New York Stock Exchange |

| INFY.BO | Bombay Stock Exchange |

+---------+-------------------------+

Above method is called implicit Join an d This query can also be written by using explicit join style which uses ON clause to join tables.

select s.RIC, m.NAME from Stock s INNER JOIN Market ON m I s.LISTED_ON_EXCHANGE=m.RIC;

17. Calling function on SELECT clause e.g. displaying current date

select getdate();

+---------------------+

| getdate() |

+---------------------+

| 2011-10-13 10:25:47 |

+---------------------+

18. Doing calculation using SELECT CLAUSE

You can perform some basic calculation using SELECT clause e.g addition, subtraction, multiplication, division etc.

select 1+2;

+-----+

| 1+2 |

+-----+

| 3 |

+-----+

Microsoft SQL Server

Thursday, 12 March 2015

Select Clause SQL Server Tutorial and Interview Questions

No comments:

Post a Comment

Total Pageviews