Concept questions
Coding Questions
- What is a stored procedure?
2. What is a function in SQL Server?
Answer: A function is a sequence of statements that accepts input, processes them to perform a specific task and provides the output. Functions must have a name but the function name can never start with a special character such as @, $, #, and so on.
Types of functions:
Pre-Defined Function
User-Defined Function
Question 3: What is the difference between Stored procedures and functions
Question 4: What is a view in the database?
Answer: A View is nothing but a select query with a name given to it or we can simply say a view is a Named Query. Ok! Why do we need a view? There can be many answers for this. Some of the important stuff I feel is:
A view can combine data from multiple tables using adequate joins and while bringing it may require complex filters and calculated data to form the required result set. From a user's point of view, all these complexities are hidden data queried from a single table.
Sometimes for security purposes, access to the table, table structures and table relationships are not given to the database user. All they have is access to a view not knowing what tables actually exist in the database.
Using the view, you can restrict the user update only portions of the records.
The following are the key points to be noted about views:
Multiple views can be created on one table.
Views can be defined as read-only or updatable.
Views can be indexed for better performance.
Insert, update, delete can be done on an updatable view.
Question 5: What is a join in SQL Server?
Answer: If You want to retrieve data from multiple tables then you need to use joins in SQL Server. Joins are used to get data from two or more tables based on the relationships among some of the columns in the tables.
A Join condition defines a way two tables are related in a query by :
Specifying the column to be used for the Join from each table. In joining foreign keys in a table and its associated key in the other table.
To use the logical operator in comparing values from the columns.
There are three type of joins available based on the way we join columns of two different tables.
Full Join
Inner Join
Left outer Join
Right outer Join
Full Join
A full join is somewhat different from the Cartesian product. A Cartesian product will get all the possible row combinations of the two joining tables. A Full Join takes the matching columns plus all table rows from the left table that does not match the right and all table rows in the right that does not match the left. It applies null for unmatched rows on the other end when doing so. The following example shows the full join between Table_A and Table_C
Inner or Self Join: This Join returns a row when there is at least one match in both tables.
Left Outer Join
A LEFT OUTER JOIN is one of the JOIN operations that allows you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.
Right Outer Join
A RIGHT OUTER JOIN is one of the JOIN operations that allows you to specify a JOIN clause. It preserves the unmatched rows from the Table2 (right) table, joining them with a NULL in the shape of the Table1 (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order.
Full Outer Join
FULL OUTER JOIN: This JOIN is a combination of both. All records from both Left_Table and Right_Table are in the result set and matched when they can be on the Join_Condition; when no record is found in the opposite table, NULL values are used for the columns.
Left Join: A LEFT OUTER JOIN is one of the JOIN operations that allows you to specify a join clause.
It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.
Right Join: A RIGHT OUTER JOIN is one of the JOIN operations that allows you to specify a JOIN clause. It preserves the unmatched rows from the Table2 (right) table, joining them with a NULL in the shape of the Table1 (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order.
Question 6: What are the integration services in SQL Server?
Answer: Integration Services is a platform for building high performance data integration and workflow solutions, including extraction, transformation and loading (ETL) operations for data warehousing.
Question 7: What are the reporting services in SQL Server?
Answer: SQL Server Reporting Services is a comprehensive reporting platform that includes processing components. Processing components are the basis for the multilayered architecture of SQL Server Reporting Services. Processing components interact with each other to retrieve data and deliver a report.
Question 8: How to execute dynamic SQL?
Answer:
1. Writing a query with parameters.
2. Using EXEC.
3. Using sp_executesql
How can you delete duplicate records in a table with no primary key?
Use the SET ROWCOUNT command. So if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services? If so, what are the commands?
Yes - Six commands are available to import data directly in the T-SQL language. These commands include:
1. BCP
2. Bulk Insert
3. OpenRowSet
4. OPENDATASOURCE
5. OPENQUERY
6. Linked Servers
Find student _id 10 and 7 in table dbo.MathScore
Answer:
-- solution 3
declare @N int = 5;
select * from dbo.MathScore
where score in (select distinct top (@N) score from dbo.MathScore order by score desc
except
select distinct top (@N-1) score from dbo.MathScore order by score desc
)
order by score desc
-- solution 1
declare @N int = 5;
select * from dbo.MathScore m1
where (select count(distinct score) from dbo.MathScore m2 where m1.Score >= m2.Score) = @N;
go
-- solution 2, using Window function and is applicable to sql server 2005+
declare @N int = 5;
; with c as (
select rnk=rank() over (order by score desc), *
from dbo.MathScore
)
select student_id, score
from c
where rnk = @N;
go
Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?
SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X
No comments:
Post a Comment