SQL
Is a NULL value same as zero or a blank space? If not then what is the difference?
A NULL value is not same as zero or a blank space. A NULL value is a value which is 'unavailable, unassigned, unknown or not applicable'. Whereas, zero is a number and blank space is a character.
What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
Scalar functions
A scalar function returns a single value based on the input value. LEN() - Calculates the total length of the given field (column). UCASE() - Converts a collection of string values to uppercase characters. LCASE() - Converts a collection of string values to lowercase characters. MID() - Extracts substrings from a collection of string values in a table. CONCAT() - Concatenates two or more strings. RAND() - Generates a random collection of numbers of given length. ROUND() - Calculates the round off integer value for a numeric field (or decimal point values). NOW() - Returns the current data & time. FORMAT() - Sets the format to display a collection of values.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
What are the properties of Sub-Queries?
A sub-query must be enclosed in the parenthesis. A sub-query must be put in the right hand of the comparison operator, and A sub-query cannot contain an ORDER-BY clause. A query can contain more than one sub-query.
What do you understand by a subquery? When is it used?
A subquery is a SELECT statement embedded in a clause of another SELECT statement. It is used when the inner query, or the subquery returns a value that is used by the outer query. It is very useful in selecting some rows in a table with a condition that depends on some data which is contained in the same table.
What is Nested Trigger?
A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
26. What is a View?
A view in SQL is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
What happens if you omit the WHERE clause in a delete statement?
All the rows in the table are deleted.
What happens if you omit the WHERE clause in the UPDATE statement?
All the rows in the table are modified.
Given these contents of the Customers table: Id Name ReferredBy 1 John Doe NULL 2 Jane Smith NULL 3 Anne Jenkins 2 [...] Here is a query written to return the list of customers not referred by Jane Smith: SELECT Name FROM Customers WHERE ReferredBy <> 2; What will be the result of the query? Why? What would be a better way to write it?
Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don't show up. But certainly those customers weren't referred by Jane Smith, and certainly NULL is not equal to 2, so why didn't they show up? The query should be written in one of two ways: SELECT Name FROM Customers WHERE ReferredBy IS NULL OR ReferredBy <> 2 ...or: SELECT Name FROM Customers WHERE ISNULL(ReferredBy, 0) <> 2; -- (Or COALESCE() )
25. What is an Alias in SQL?
An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields. A table alias is also called a correlation name .
What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, whereas unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.
What is 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
What are various DCL commands in SQL? Give brief description of their purposes
Data Control Language commands in SQL − GRANT − it gives a privilege to user. REVOKE − it takes back privileges granted from user
What are various DML commands in SQL? Give brief description of their purposes.
Data Manipulation Language commands in SQL − SELECT − it retrieves certain records from one or more tables. INSERT − it creates a record. UPDATE − it modifies records. DELETE − it deletes records
What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
SQL query to transpose text C A P O N E
Declare @a nvarchar(100)='capone'; Declare @length INT; Declare @i INT=1; SET @lenght=LEN(@a) while @i<=@length BEGIN print(substring(@a,@i,1)); set @i=@i+1; END
What conditions must be met to use union, minus, intersect commands?
Each SELECT statement within the clause must have the same number of columns The columns must also have similar data types The columns in each SELECT statement should necessarily have the same order
23. What are Entities and Relationships?
Entity: An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity. Relationships: Relations or links between entities that have something to do with each other. For example - The employees table in a company's database can be associated with the salary table in the same database.
Say True or False. Give explanation if False. Group functions cannot be nested.
False. Group functions can be nested to a depth of two.
Say True or False. Give explanation if False. INSERT statement does not allow copying rows from one table to another.
False. INSERT statement allows to add rows to a table copying rows from an existing table.
Say True or False. Give explanation if False. The DELETE statement is used to delete a table from the database.
False. The DELETE statement is used for removing existing rows from a table.
What's wrong in the following query? SELECT student_code, name FROM students WHERE marks = (SELECT MAX(marks) FROM students GROUP BY subject_code);
Here a single row operator = is used with a multiple row subquery.
What is the difference between IN and EXISTS?
IN: - Works on List result set - Doesn't work on subqueries resulting in Virtual tables with multiple columns - Compares every value in the result list - Performance is comparatively SLOW for larger resultset of subquery EXISTS: - Works on Virtual tables - Is used with co-related queries - Exits comparison when match is found - Performance is comparatively FAST for larger resultset of subquery
How do you copy data from one table to another table ?
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
How to get @@ERROR and @@ROWCOUNT at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
How do you get the Nth-highest salary from the Employee table without a subquery or CTE?
If we want the 3rd highest salaray: SELECT salary from Employee order by salary DESC LIMIT 2,1 OR SELECT salary from Employee order by salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY
How do you insert null values in a column while inserting data?
Implicitly by omitting the column from the column list. Explicitly by specifying the NULL keyword in the VALUES clause.
What is OLTP (Online Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
Given two tables created as follows create table test_a(id numeric); create table test_b(id numeric); insert into test_a(id) values (10), (20), (30), (40), (50); insert into test_b(id) values (10), (30), (50); Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.
In SQL Server, PostgreSQL, and SQLite, this can be done using the except keyword as follows: select * from test_a except select * from test_b;
How do you search for a value in a database table when you don't have the exact value to search for?
In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called 'wildcard' search.
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server.
35. What is OLTP?
OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. To avoid single points of failure, OLTP systems are often decentralized. These systems are usually designed for a large number of users who conduct short transactions.
What are the differences between OLTP and OLAP?
OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. These systems are generally designed for a large audience of end users who conduct short transactions. Queries involved in such databases are generally simple, need fast response times and return relatively few records. OLAP stands for Online Analytical Processing, a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations.
24. List the different types of relationships in SQL.
One-to-One - This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table. One-to-Many & Many-to-One - This is the most commonly used relationship where a record in a table is associated with multiple records in the other table. Many-to-Many - This is used in cases when multiple instances on both sides are needed for defining a relationship. Self Referencing Relationships - This is used when a table needs to define a relationship with itself.
3. What is RDBMS? How is it different from DBMS?
RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2 and Amazon Redshift are based on RDBMS.
Write an SQL Query to find the name of an employee whose name Start with 'M'
SELECT * FROM Employees WHERE EmpName like 'M%';
Table is as follows: ID C1 C2 C3 1 Red Yellow Blue 2 NULL Red Green 3 Yellow NULL Violet Print the rows which have 'Yellow' in one of the columns C1, C2, or C3, but without using OR.
SELECT * FROM table WHERE 'Yellow' IN (C1, C2, C3)
find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe
SELECT * from Employees WHERE UPPER(EmpName) like '%JOE%'; Made the EmpName uppercase to compare
Write an SQL Query to find the number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975
SELECT COUNT(*), sex FROM Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;
Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975';
Write an SQL Query to find an employee whose salary is equal to or greater than 10000
SELECT EmpName FROM Employees WHERE Salary>=10000;
Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer. (Note: You may assume that there are at least 10 records in the Employee table.)
SELECT TOP (1) Salary FROM ( SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary This works as follows: First, the SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC query will select the top 10 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not the the 10th highest salary. Therefore, the second query reorders the 10 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 10 salaries).
Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z, A-Z). Write a SQL query to print 'Fizz' for a numeric value or 'Buzz' for alphabetical value for all values in that column. Example: ['d', 'x', 'T', 8, 'a', 9, 6, 2, 'V'] ...should output: ['Buzz', 'Buzz', 'Buzz', 'Fizz', 'Buzz','Fizz', 'Fizz', 'Fizz', 'Buzz']
SELECT col, case when upper(col) = lower(col) then 'Fizz' else 'Buzz' end as FizzBuzz from table;
different Types of Sub-Queries?
Single-row sub-query, where the sub-query returns only one row. Multiple-row sub-query, where the sub-query returns multiple rows,. and Multiple column sub-query, where the sub-query returns multiple columns
Say True or False. Give explanation if False. You can use a subquery in an INSERT statement.
T
What is the difference between cross joins and natural joins?
The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What does UNION do? What is the difference between UNION and UNION ALL?
UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records. It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.
What is the difference between VARCHAR2 AND CHAR datatypes?
VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.
What are the properties of the Relational tables?
Values are atomic. Column values are of the same kind. Each row is unique. The sequence of columns is insignificant. The sequence of rows is insignificant. Each column must have a unique name.
What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
What is the difference between char and varchar2?
When stored in a database, varchar2 uses only the allocated space. E.g. if you have a varchar2(1999) and put 50 bytes in the table, it will use 52 bytes. But when stored in a database, char always uses the maximum length and is blank-padded. E.g. if you have char(1999) and put 50 bytes in the table, it will consume 2000 bytes.
What are the authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.
Can we insert a row for identity column implicitly?
Yes, like so: SET IDENTITY_INSERT TABLE1 ON INSERT INTO TABLE1 (ID,NAME) SELECT ID,NAME FROM TEMPTB1 SET IDENTITY_INSERT OFF
Can you remove rows from a table based on values from another table? Explain.
Yes, subqueries can be used to remove rows from a table based on values from another table.
How to find a duplicate record?
duplicate records with one field: SELECT name, COUNT(email) FROM users GROUP BY email HAVING COUNT(email) > 1 duplicate records with more than one field SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1
SQL Query to find the second highest salary of Employee
here we first select maximum salary and then another maximum excluding result of subquery: SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (select MAX(Salary) from Employee ); OR: SELECT salary FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1
Given a table TBL with a field Nmbr that has rows with the following values: 1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1 Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.
update TBL set Nmbr = case when Nmbr = 0 then Nmbr+2 else Nmbr+3 end;
Write a query to insert/update Col2's values to look exactly opposite to Col1's values. Col1 Col2 1 0 0 1 0 1 0 1 1 0 0 1 1 0 1 0
update table set col2 = case when col1 = 1 then 0 else 1 end Or if the type is numeric: update table set col2 = 1 - col1
Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 desc;
"Order by 2" is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so "Order by 2" will cause the statement to throw an error while executing the above sql query.
SQL Query to Find Employees Earning More Than Managers
+----+-------+--------+-----------+| Id | Name | Salary | ManagerId |+----+-------+--------+-----------+| 1 | Joe | 70000 | 3 || 2 | Henry | 80000 | 4 || 3 | Sam | 60000 | NULL || 4 | Max | 90000 | NULL |+----+-------+--------+-----------+ To find out managers, we create two instances of Employee table e1 and e2 and compare e1.ManagerId = e2.Id to find all managers, the next condition is just to compare their salaries. SELECT e1.name FROM Employee e1 JOIN Employee e2 ON e1.ManagerId = e2.Id WHERE e1.salary > e2.salary
What is CHECK Constraint?
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
Types of subquery
A correlated subquery cannot be considered as an independent query, but it can refer the column in a table listed in the FROM of the main query. A non-correlated subquery can be considered as an independent query and the output of subquery is substituted in the main query.
17. What is a Query?
A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.
If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?
A query result displays all rows including the duplicate rows. To eliminate duplicate rows in the result, the DISTINCT keyword is used in the SELECT clause.
What are the different index configurations a table can have?
A table can have one of the following index configurations: No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes
List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.
What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
2. What is DBMS?
DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation and management of the database. It ensures that our data is consistent, organized and is easily accessible by serving as an interface between the database and its end users or application softwares.
How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?
Declare @N int set @N = 5; WITH CTE AS ( SELECT Name, Salary, EmpID, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM Employee ) SELECT Name, Salary, EmpID FROM CTE WHERE RN = @N
Say True or False. Give explanation if False. A DROP TABLE statement can be rolled back.
False
Say True or False. Give explanation if False. By default the group functions consider only distinct values in the set.
False. By default, group functions consider all values including the duplicate values.
Say True or False. Give explanation if False. A single row subquery returns only one row from the outer SELECT statement
False. A single row subquery returns only one row from the inner SELECT statement.
Say True or False. Give explanation if False. COUNT(*) returns the number of columns in a table.
False. COUNT(*) returns the number of rows in a table.
What is the purpose of the group functions in SQL? Give some examples of group functions.
Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
What's wrong in the following query? SELECT subject_code, count(name) FROM students;
It doesn't have a GROUP BY clause. The subject_code should be in the GROUP BY clause. SELECT subject_code, count(name) FROM students GROUP BY subject_code;
What are the case manipulation functions of SQL?
LOWER, UPPER, INITCAP
What are the specific uses of SQL functions?
Performing calculations on data Modifying individual data items Manipulating the output Formatting dates and numbers Converting data types
INNER JOIN
Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.
SQL Query to find Max Salary from each department. Print DeptID
SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID.
Write SQL Query to display the current date
SELECT GetDate();
Write an SQL Query to find the year from date.
SELECT YEAR(GETDATE()) as "Year";
What is a SQL statement equal to the following: SELECT name FROM customer WHERE state = 'VA';
SELECT name FROM customer WHERE state IN ('VA');
What is the STUFF function and how does it differ from the REPLACE function?
STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.
What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
Select distinct records: SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno) to Delete: DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
Which statement is used to add a new row in a database table?
The INSERT INTO statement.
How do you copy rows from one table to another?
The INSERT statement can be used to add rows to a table by copying from another table. In this case, a subquery is used in the place of the VALUES clause.
What is the use of the NULLIF function?
The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the first expression is returned.
What is the purpose of the NVL function?
The NVL function converts a NULL value to an actual value.
What's wrong in the following query? SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;
The WHERE clause cannot be used to restrict groups. The HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;
Consistency
The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
What is the default ordering of data using the ORDER BY clause? How could it be changed?
The default sorting order is ascending. It can be changed using the DESC keyword, after the column name in the ORDER BY clause.
Isolation
The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction
You want to display a result query from joining two tables with 20 and 10 rows respectively. Erroneously you forget to write the WHERE clause. What would be the result?
The result would be the Cartesian product of two tables with 20 x 10 = 200 rows.
How can you select all the even number records from a table? All the odd number records?
To select all the even number records from a table: Select * from table where id % 2 = 0 To select all the odd number records from a table: Select * from table where id % 2 != 0
What is a view? Why should you use a view?
]Restricting access to data; Making complex queries simple; Ensuring data independency; Providing different views of same data.
Given the following table named A: x ------ 2 -2 4 -4 -3 0 2 Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x.
select sum(case when x>0 then x else 0 end)sum_pos,sum(case when x<0 then x else 0 end)sum_neg from a;
Given the table mass_table: weight 5.67 34.567 365.253 34 Write a query that produces the output: weight kg gms 5.67 5 67 34.567 34 567 365.253 365 253 34 34 0
select weight, trunc(weight) as kg, nvl(substr(weight - trunc(weight), 2), 0) as gms from mass_table;
What are different normalization forms?
1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be in DKNF. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
Given this table: Testdb=# Select * FROM "Test"."EMP"; ID ---- 1 2 3 4 5 (5 rows) What will be the output of below snippet? Select SUM(1) FROM "Test"."EMP"; Select SUM(2) FROM "Test"."EMP"; Select SUM(3) FROM "Test"."EMP";
5 10 15
What is FOREIGN KEY?
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
What are the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
1. What is Database?
A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.
What is the difference between a Local and a Global temporary table?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
6. What are Tables and Fields?
A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Aggregate functions
An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. AVG() - Calculates the mean of a collection of values. COUNT() - Counts the total number of records in a specific table or view. MIN() - Calculates the minimum of a collection of values. MAX() - Calculates the maximum of a collection of values. SUM() - Calculates the sum of a collection of values. FIRST() - Fetches the first element in a collection of values. LAST() - Fetches the last element in a collection of values.
What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server's query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure. In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN
What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
Atomicity
Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
What are the NVL and the NVL2 functions in SQL? How do they differ?
Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null. With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1. With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.
What are different types of Collation Sensitivity?
Case sensitivity - A and a, B and b, etc. Accent sensitivity Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.
7. What are Constraints in SQL?
Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are: NOT NULL - Restricts NULL value from being inserted into a column. CHECK - Verifies that all values in a field satisfy a condition. DEFAULT - Automatically assigns a default value if no value has been specified for the field. UNIQUE - Ensures unique values to be inserted into the field. INDEX - Indexes a field providing faster retrieval of records. PRIMARY KEY - Uniquely identifies each record in a table. FOREIGN KEY - Ensures referential integrity for a record in another table
40. How to create empty tables with the same structure as another table?
Creating empty tables with the same structure can be done smartly by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table. SELECT * INTO Students_copy FROM Students WHERE 1 = 2;
30. What are the TRUNCATE, DELETE and DROP statements?
DELETE statement is used to delete rows from a table. DELETE FROM Candidates WHERE CandidateId > 1000; TRUNCATE command is used to delete all the rows from the table and free the space containing the table. TRUNCATE TABLE Candidates; DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. DROP TABLE Candidates;
What are various DDL commands in SQL? Give brief description of their purposes.
Data Definition Language commands in SQL − CREATE − it creates a new table, a view of a table, or other object in database. ALTER − it modifies an existing database object, such as a table. DROP − it deletes an entire table, a view of a table or other object in the database.
16. What is Data Integrity?
Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
Durability
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.
What are the various multiple row comparison operators in SQL?
IN, ANY, ALL.
What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
You have a composite index of three columns, and you only provide the value of two columns in the WHERE clause of a select query? Will Index be used for this operation? For example, if Index is on EmpId, EmpFirstName, and EmpSecondName and you write a query like SELECT * FROM Employee WHERE EmpId=2 and EmpFirstName='Radhe'
If the given two columns are secondary index columns then the index will not invoke, but if the given 2 columns contain the primary index(first column while creating index) then the index will invoke. In this case, the Index will be used because EmpId and EmpFirstName are primary columns.
How do you get the last id without the max function?
In SQL Server: select top 1 id from table order by id desc
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
CROSS JOIN
Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax ("explicit join notation") or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria ("implicit join notation").
FULL JOIN (or FULL OUTER JOIN)
Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.
LEFT JOIN (or LEFT OUTER JOIN)
Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table. This means that if the ON clause doesn't match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN)
Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn't find any matching records in the left table. This means that if the ON clause doesn't match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.
Given the following tables: SELECT * FROM users; user_id username 1 John Doe 2 Jane Don 3 Alice Jones 4 Lisa Romero SELECT * FROM training_details; user_training_id user_id training_id training_date 1 1 1 "2015-08-02" 2 2 1 "2015-08-03" 3 3 2 "2015-08-02" 4 4 2 "2015-08-04" Write a query to to get the list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.
SELECT u.user_id, username, training_id, training_date, count(user_training_id) AS count FROM users u JOIN training_details t ON t.user_id = u.user_id GROUP BY u.user_id, username, training_id, training_date HAVING count( user_training_id ) > 1 ORDER BY training_date DESC;
Name 3 ways to get an accurate count of the number of records in a table?
SELECT * FROM table1 SELECT COUNT(*) FROM table1 SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').
Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table? (Assume the table contains well over 100 records with odd user_id values.)
SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id
print the name of the employee and their manager
SELECT e1.name, e2.name as Manager FROM Employee e1 JOIN Employee e2 ON e1.Id = e2.ManagerId
Invoices: column name | data type | allow nulls id | int | no BillingDate | date | no CustomerId | int | no Customers: column name | data type | allow nulls id | int | no name | nvarchar(50) | no ReferredBy | int | yes write a SQL query to return a list of all the invoices. For each invoice, show the Invoice ID, the billing date, the customer's name, and the name of the customer who referred that customer (if any). The list should be ordered by billing date
SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName FROM Invoices i JOIN Customers c ON i.CustomerId = c.Id LEFT JOIN Customers r ON c.ReferredBy = r.Id ORDER BY i.BillingDate; Did the candidate remember to use a LEFT JOIN instead of an inner JOIN when joining the customer table for the referring customer name? If not, any invoices by customers not referred by somebody will be left out altogether. Note that this query will not return Invoices that do not have an associated Customer. This may be the correct behavior for most cases (e.g., it is guaranteed that every Invoice is associated with a Customer, or unmatched Invoices are not of interest). However, in order to guarantee that all Invoices are returned no matter what, the Invoices table should be joined with Customers using LEFT JOIN: SELECT i.Id, i.BillingDate, c.Name, r.Name AS ReferredByName FROM Invoices i LEFT JOIN Customers c ON i.CustomerId = c.Id LEFT JOIN Customers r ON c.ReferredBy = r.Id ORDER BY i.BillingDate;
19. What is the SELECT statement?
SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set. SELECT * FROM myDB.students;
here is a table which contains two columns Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above-average students.
SELECT student, marks FROM table WHERE marks > SELECT AVG(marks) from table)
Suppose we have a Customer table containing the following data: CustomerID CustomerName 1 Prashant Kaurav 2 Ashish Jha 3 Ankit Varma [...] Write a single SQL statement to concatenate all the customer names into the following single semicolon-separated string: Prashant Kaurav; Ashish Jha; Ankit Varma; Vineet Kumar; Rahul Kumar
SELECT top 1 LTRIM(STUFF((SELECT '; ' + c1.CustomerName FROM Customer c1 FOR XML PATH ('')), 1, 1,'')) as SSV from Customer c2;
What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
What is SQL Server Agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.
Can SQL Servers linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.
Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
Write an SQL Query to check whether the date passed to Query is the date of the given format or not
SQL has IsDate() function which is used to check passed value is a date or not of specified format, it returns 1(true) or 0(false) accordingly. Remember the ISDATE() is an MSSQL function and it may not work on Oracle, MySQL, or any other database but there would be something similar. SELECT ISDATE('1/08/13') AS "MM/DD/YY";
5. What is the difference between SQL and MySQL?
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
What is the difference between SQL and MySQL or SQL Server?
SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft's SQL Server both are relational database management systems that use SQL as their standard relational database language.
41. What is Pattern Matching in SQL?
SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information. SELECT * FROM students WHERE first_name LIKE 'K%' WHERE first_name NOT LIKE 'K%' WHERE first_name LIKE '%K%'
4. What is SQL?
SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.
Types of user-defined functions
Scalar Function: As explained earlier, user-defined scalar functions return a single scalar value. Table Valued Functions: User-defined table-valued functions return a table as output. Inline: returns a table data type based on a single SELECT statement. Multi-statement: returns a tabular result-set but, unlike inline, multiple SELECT statements can be used inside the function body.
What is the difference between single-row functions and multiple-row functions? What is the group by clause used for?
Single-row functions work with single row at a time. Multiple-row functions work with data of multiple rows at a time. The group by clause combines all those records that have identical values in a particular field or any group of fields.
What are the advantages of using Stored Procedures?
Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.
What is DataWarehousing?
Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together; Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time; Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting. Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
Given the following tables: sql> SELECT * FROM runners; +----+--------------+ | id | name | +----+--------------+ | 1 | John Doe | | 2 | Jane Doe | | 3 | Alice Jones | | 4 | Bobby Louis | | 5 | Lisa Romero | +----+--------------+ sql> SELECT * FROM races; +----+----------------+-----------+ | id | event | winner_id | +----+----------------+-----------+ | 1 | 100 meter dash | 2 | | 2 | 500 meter dash | 3 | | 3 | cross-country | 2 | | 4 | triathalon | NULL | +----+----------------+-----------+ What will be the result of the query below? SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)
Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table. Knowing this, a query that avoids this issue would be as follows: SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races WHERE winner_id IS NOT null)
Say True or False. Give explanation if False. A DDL statement or a DCL statement is automatically committed.
T
Say True or False. Give explanation if False. A multiple row subquery returns more than one row from the inner SELECT statement.
T
Say True or False. Give explanation if False. Attempting to delete a record with a value attached to an integrity constraint, returns an error.
T
Say True or False. Give explanation if False. Multiple column subqueries return more than one column from the inner SELECT statement.
T
Say True or False. Give explanation if False. While inserting new rows in a table you must list values in the default order of the columns.
T
Say True or False. Give explanation if False. A view doesn't have data of its own.
T
Say True or False. Give explanation if False. All group functions ignore null values.
T
Say True or False. Give explanation if False. If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.
T
Which SQL statement is used to add, modify or drop columns in a database table?
The ALTER TABLE statement.
What is the purpose of the condition operators BETWEEN and IN?
The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
Discuss the syntax and use of the COALESCE function?
The COALESCE function has the expression COALESCE(exp1, exp2, .... expn) It returns the first non-null expression given in the parameter list.
What is the pupose of DML statements in SQL?
The DML statements are used to add new rows to a table, update or modify data in existing rows, or remove existing rows from a table.
What is the purpose of the MERGE statement in SQL?
The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.
Which function returns the remainder in a division operation?
The MOD function returns the remainder in a division operation.
21. What are UNION, MINUS and INTERSECT commands?
The UNION operator combines and returns the result-set retrieved by two or more SELECT statements. The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first. The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.
What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.
The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a "tie"; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive "ranks" to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie). For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.
Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name). If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query: Select * From Emp, Dept
The query will result in 50 rows as a "cartesian product" or "cross join", which is the default whenever the 'where' clause is omitted.
Given two tables created and populated as follows: CREATE TABLE dbo.envelope(id int, user_id int); CREATE TABLE dbo.docs(idnum int, pageseq int, doctext varchar(100)); INSERT INTO dbo.envelope VALUES (1,1), (2,2), (3,3); INSERT INTO dbo.docs(idnum,pageseq) VALUES (1,5), (2,6), (null,0); What will the result be from the following query: UPDATE docs SET doctext=pageseq FROM docs INNER JOIN envelope ON envelope.id=docs.idnum WHERE EXISTS ( SELECT 1 FROM dbo.docs WHERE id=envelope.id );
The result of the query will be as follows: idnum pageseq doctext 1 5 5 2 6 6 NULL 0 NULL The EXISTS clause in the above query is a red herring. It will always be true since ID is not a member of dbo.docs. As such, it will refer to the envelope table comparing itself to itself! The idnum value of NULL will not be set since the join of NULL will not return a result when attempting a match with any value of envelope.
When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
What will be the output of the below query, given an Employee table having 10 records? BEGIN TRAN TRUNCATE TABLE Employees ROLLBACK SELECT * FROM Employees
This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.
What is Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.
What is User Defined Functions?
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.
Which expressions or functions allow you to implement conditional processing in a SQL statement?
Using CASE expression Using the DECODE function
20. What are some common clauses used with SELECT query in SQL?
WHERE clause in SQL is used to filter records that are necessary, based on specific conditions. ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC)
Using _ wildcard
WHERE first_name LIKE '__K%' The _ wildcard matches exactly one character of any type. It can be used in conjunction with % wildcard. This query fetches all students with letter K at the third position in their first name. SELECT * /* Matches first names with three or more letters */ FROM students WHERE first_name LIKE '___%' SELECT * /* Matches first names with exactly four characters */ FROM students WHERE first_name LIKE '____'
What is the difference between the WHERE and HAVING clauses?
When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent. However, when GROUP BY is used: The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made. The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
Suppose in a table, seven records are there. The column is an identity column. Now the client wants to insert a record after the identity value 7 with its identity value starting from 10. Is it possible? If so, how? If not, why not?
Yes, it is possible, using a DBCC command: create table tableA (id int identity, name nvarchar(50) ) insert into tableA values ('ram') insert into tableA values ('rahim') insert into tableA values ('roja') [...] select * From tableA DBCC CHECKIDENT(tableA,RESEED,9) insert into tableA values ('roli') insert into tableA values ('rosy') insert into tableA values ('raka') [...]
Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
Can you sort a column using a column alias?
Yes. By the ORDER BY clause
Can you modify the rows in a table based on values from another table? Explain.
Yes. Use of subqueries in UPDATE statements allow you to update rows in a table based on values from another table.
Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?
You can avoid duplicates using UNION ALL and still run much faster than UNION DISTINCT (which is actually same as UNION) by running a query like this: SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command, while avoiding much of its performance hit.
How do you find all employees who are also managers? You are given a standard employee table with an additional column mgr_id, which contains the employee id of the manager.
You need to know about self-join to solve this problem. In Self Join, you can join two instances of the same table to find out additional details as shown below SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id; this will show employee name and manager name in two columns like: | name | manager_name | +--------+----------+---------+--------+ | David | John |
Reference schema
mysql> select * from employee; +--------+----------+---------+--------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+--------+ | 103 | Jack | 1 | 1400 | | 104 | John | 2 | 1450 | | 108 | Alan | 3 | 1150 | | 107 | Ram | NULL | 600 | +--------+----------+---------+------- mysql> select * from department; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | Sales | | 2 | Finance | | 3 | Accounts | | 4 | Marketing | +---------+-----------+
Consider the Employee table below. Emp_Id Emp_name SalaryManager_Id 10 Anil 50000 18 11 Vikas 75000 16 12 Nisha 40000 18 [...] Write a query to generate below output: Manager_Id Manager Average_Salary_Under_Manager 16 Rajesh 65000 17 Raman 62500 18 Santosh 53750
select b.emp_id as "Manager_Id", b.emp_name as "Manager", avg(a.salary) as "Average_Salary_Under_Manager" from Employee a, Employee b where a.manager_id = b.emp_id group by b.emp_id, b.emp_name order by b.emp_id;
SQL Query to find Max Salary from each department. Print DeptName
used RIGHT OUTER JOIN because we need the name of the department from the Department table which is on the right side of the JOIN clause, even if there is no reference of dept_id on the Employee table. SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId = d.DeptID GROUP BY DeptName
