The database of the interview question summary

Sql

1. There is a student's table, there is only one column is the name, please select the name of the student with the names of the names
Select name from student group by name have count (*) > 1

2. Select all employee records from the payroll table for all departments in which the average salary is greater than the average salary of the company

SELECT * FROM company where department in (select Department
From company
GROUP BY Deparment
have avg (Salary) > (select AVG (Salary) from company)
)

3.Given the following tables.
Names
Namenumber
Wayne Gretzky99
Jaromir Jagr68
Bobby ORR4
Bobby Hull23
Brett Hull16
Mario Lemieux66
Steve Yzerman19
Claude Lemieux19
Mark Messier11
Mats Sundin 13

Points
Namepoints
Wayne Gretzky244
Jaromir Jagr168
Bobby ORR 129
Bobby Hull93
Brett Hull121
Mario Lemieux109
Joe Sakic94

Write SQL statement to display the player's Names, numbers and points for all players represented by a entry in both Tabl Es?

Answer:a. SELECT names.name, Names.number, points.points from names INNER joins points on Names.name=points.name

A.select Names.name, Names.number, points.points from names INNER joins points on Names.name=points.name
B.select Names.name, Names.number, points.points from names full OUTER JOIN points on Names.name=points.name
C.select Names.name, Names.number, points.points from names to OUTER JOIN points on Names.name=points.name
D.select Names.name, Names.number, points.points from names right OUTER JOIN points on Names.name=points.name

4.Given

Staff
Idinteger
Namechar (20)
Deptinteger
Jobchar (20)
Yearsinteger
Salarydecimal (10, 2)
Comm. DECIMAL (10, 2)

Write a SQL sentence to Retrun total number of employees in each department with corresponding department ID under the fol lowing conditions:
Only return departments with at least one employee receiving a commission greater than 5000. The results should is sorted by the department count from most to least

Answer, B.select Dept, COUNT (*) from the Staff GROUP by dept have comm.>5000 ORDER by 1 DESC

A.select Dept, COUNT (ID) from the staff WHERE comm>5000 GROUP by dept ORDER by 1 DESC
B.select Dept, COUNT (*) from the Staff GROUP by dept have comm.>5000 ORDER by 1 DESC
C.select Dept, COUNT (*) from the staff WHERE comm.>5000 GROUP by dept, Comm. ORDER by 2 DESC
D.select Dept, Comm, COUNT (ID) from the staff WHERE comm.>5000 GROUP by dept, Comm ORDER by 3 DESC

5.Given The following table definitions

ORG
Deptnumb INTEGER
Deptnamechar (30)
Managerinteger
Divisionchar (30)
Locationchar (30)

Staff
Idinteger
Namechar (30)
Deptinteger
Jobchar (20)
Years INTEGER
Salarydecimal (10, 2)
Commdecimal (10, 2)

Write a SQL statement to display each department by name, and the total salary of all employees in the department?

Answer, C.select a.deptname, SUM (b.salary) from Org A, staff b WHERE a.deptnumb=b.dept GROUP by A.deptname

A.select A.deptname, SUM (b.salary) from Org A, staff b WHERE a.deptnumb=b.dept ORDER by A.deptname
B.select B.deptname, SUM (a.salary) from Org A, staff b WHERE a.deptnumb=b.dept ORDER by A.deptname
C.select A.deptname, SUM (b.salary) from Org A, staff b WHERE a.deptnumb=b.dept GROUP by A.deptname
D.select B.deptname, SUM (a.salary) from Org A, staff b WHERE a.deptnumb=b.dept GROUP by A.deptname

Understanding of table, view and index.

Given tables as follows:

T1
Col1 Col2
1 10
2 20
3 20
4 30
5 30
6 30

T2
Col1 Col2
1 10
2 20
3 30
4 40
5 50
6 60

1. Please write a query based on T1 and T2 which produces:
ColA ColB
1 10
2 20
3 20
3 30
4 30
4 40
5 30
5 50
6 30
6 60
(hint:the result set is produced from merging T1 and T2 together, without duplicate values)

2. Please write a query based on T1 which produces:

Col2

20

30

Note that the DON ' T use hard coded ' WHERE clause ' to simply ' select distinct col2 from t1 where col2 = or col2 = 30 '.

(Hint:20 and apprear more than once in col2 of T1)

3. Please write the result of the following query:
Select A.col1, B.col1, A.col2, b.col2 from T1 A full OUTER JOIN T2 B on (a.col2 = b.col2);

4. Please list the database objects your know (for example, table is one of the objects). Describe How to use them and why does you use them.

5. How many kinds of the keys in database? How do I use them?

6. We want to prevent the users from inserting the values larger than of col1 in T1 (above table). How does that?

7. User A is doing some modifications on T1, and he does not want other users to know what he had changed until he commit All he work. What options could he has?

8. How to return the values from a store procedure? How to return result set from a store procedure?

9. How does I add another column Col3 to T1, with data type integer and default value 100?

Describe How to use the ' CURSOR ' in SQL procedure. Please write a procedure this uses CURSOR to return result set with ' select col1 from T1 '.

General Questions of SQL SERVER

What is the Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
DEALLOCATE cursor (Read more here)

What is Collation?
Collation refers to a set of rules, which determine how data is sorted and compared. Character data is sorted using rules which define the correct Character sequence, with options for specifying case Sensitiv ity, accent marks, kana character types and character width. (Read more here)
What is difference between Function and Stored Procedure?
UDF can is used in the SQL statements anywhere in the Where/having/select section WHERE as Stored procedures cannot is. UDFs that return tables can is treated as another rowset. This can is used in JOINs with other tables. The Inline UDF's can is thought of as views the take parameters and can is used in JOINs and other Rowset operations.

What is Sub-query? Explain Properties of Sub-query?
Sub-queries is often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the B Ody of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries is generally used to return a single row as an atomic value, though they is used to compare values agains T multiple rows with the In keyword.
A subquery is a SELECT statement this is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, would return a results Et. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can is found in, the column list of a SELECT statement, a From, GROUP by, have, and/or ORDER by clauses of a T-SQL statement. A subquery can also is used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used. (Read more here)

What is different Types of Join?

Cross Join
A cross join this does not has a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in th e second table. The common example is while company wants to combine each product with a pricing table to analyze each product at each Pric E.

Inner Join
A join that displays only the rows that has a match in both joined tables is known as INNER join. The the default type of join in the Query and View Designer.

Outer Join
A join that includes rows even if they does not has related rows in the joined table was an Outer Join. &nb Sp You can create three different outer join to specify the unmatched rows to being included:
left outer join:in left outer Join all rows in the First-named table i.e. ' Left ' table, which appears leftmost in the join clause is included. Unmatched rows in the right table does not appear. ,
right Outer join:in right Outer Join all rows in the Second-nam Ed table i.e. "right" table, which appears rightmost in the JOIN clause is included. Unmatched rows in the left table is not included.
Full Outer join:in full Outer Join All rows in all joined tables is included, whether they is matched or not.

Self Join
This was a particular case when one table joins to itself, with one or both aliases to avoid confusion. A self Join can is of any type, as long as the joined tables is the same. A self join was rather unique in the IT involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another. The self join can be Outer join or Inner join. (Read more here)

What is primary keys and foreign keys?
Primary Keys is the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys is the most fundamental of all keys and constraints. A table can has only one Primary key.
Foreign Keys is both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is User Defined Functions? What kind's user-defined Functions can be created?
user-defined Functions allow defining its own T-SQL Functions that can accept 0 or more parameters and return a single Scalar data value or a table data type.
Different kinds of user-defined Functions created are: 

Scalar user-defined function
A scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types is not supported. These is the type of user-defined functions that most developers is used to and other programming languages. You pass the 0 to many parameters and you get a return value.
Inline Table-value user-defined function
An Inline table-value user-defined function returns a Table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL SELECT command and I n Essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement table-value user-defined function
A multi-statement table-value user-defined function returns a Table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the Final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL Select command or a group of them gives us the capability-in essence C Reate a parameterized, non-updateable view of the data in the underlying tables. Within the CREATE FUNCTION command you must define the table structure it is being returned. After creating this type of user-defined function, It can is used in the FROM clause of a T-SQL command unlike the Behavio R found when using a stored procedure which can also return record sets. (Read here for Example)

What's Identity?
Identity (or AutoNumber) is a column, that automatically generates numeric values. A start and increment value can is set, but most DBAs leave these at 1. A GUID column also generates numbers; The value of this cannot is controlled. Identity/guid columns do not need to be indexed.

What is Datawarehousing?
subject-oriented, meaning that the data in the database are organized so and all the data elements relating to the same re Al-world event or object is linked together;
Time-variant, meaning the changes to the data in the database is tracked and recorded so, reports can is produce D showing changes over time;
Non-volatile, meaning that data in the database was never over-written or deleted, once committed, the data is static, read -only, but retained for the future reporting.
Integrated, meaning that the database contains data from a most or any of an organization ' s operational applications, and th At this data is made consistent.

The database of the interview question summary