1
Q
DELETE and TRUNCATE
A
DELETE
- used to delete a row in a table
- you can rollback data after delete
- DML comand
- slower
TRUNCATE
- delete all the rows in a table
- cannot rollback data
- DDL command
- faster
2
Q
Subsets of SQL
A
3
Q
What do you mean by DBMS? What are its different types?
A
Hierarchial
Network
Relational
Object-Oriented
4
Q
What do you mean by table and field in SQL?
A
Table refers to a collection of data in an organised manner in form of rows and columns
Field refers to the number of columns in a table
5
Q
What are joins in SQL?
A
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there.
Inner Join
Full Join
Left Join
Right Join
6
Q
CHAR and VARCHAR2 datatype difference
A
Char is used for strings of fixed length
Varchar2 is used for character strings of variable length
7
Q
What is a Primary key?
A
A specific choice of a minimal set of attributes that uniquely specify a tuple in a relation.
8
Q
What are constraints?
A
Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement.
NOT NULL
UNIQUE
CHECK
DEFAULT
INDEX
9
Q
What is the difference between SQL and MySQL?
A
Structured Query Language
MySQL is an open-source relational database management system that works on many platforms. It provides multi-user access to support many storage engines and is backed by Oracle.
10
Q
What is a unique key?
A
Uniquely identifies a single row in the table.
Multiple values allowed per table.
Null values are allowed.
Duplicate values are not allowed.
11
Q
What is a foreign key?
A
Foreign key maintains referential integrity by enforcing a link between the data in two tables.
The foreign key in the child table references the primary key in the parent table.
The foreign key constraint prevents actions that would destroy links between the child and parent tables.
12
Q
What do you mean by data integrity?
A
Accuracy of data
Consistency of data
Integrity Constraints to enforce buisness rules on data
13
Q
What is the difference between clustered and non clustered index in SQL?
A
Clustered Index
- clustered index is used for easy retrieval of data from the database and is faster
- clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index
- one table can only have one clustered index
Non Clustered Index
- non-clustered index is used for easy retrieval of data from the database and is slower
- non-clustered index does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching
- one table can have many non-clustered indexes
14
Q
Write a SQL query to display the current date?
A
GetDate()
SELECT GETDATE();
Result: ‘2019-03-18 18:17:26.160’
15
Q
What are the different type of joins?
A
Inner Join
Full Join
Left Join
Right Join
16
Q
What do you mean by Denormalization?
A
- Refers to a technique which is used to access data from higher to lower forms of a database.
- Increase the performance of the entire infrastructure as it introduces redundancy into a table.
- Adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.
17
Q
What are Entities?
A
A person, place, or thing about which data can be stored in a database. Tables store data that represents one type of entity.
18
Q
What are relationships?
A
Relation or links between entities that have something to do with each other.
19
Q
What is an index?
A
- Performance tuning method
- Allows faster retrieval of records from the table
- Creates an entry for each value
20
Q
Explain different types of index.
A
Unique Index
- This index does not allow the field to have duplicate values if the column is unique index. If a primary key is defined, a unique index can be applied automatically.
Clustered Index
- This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.
Non-clustered Index
- Non-clustered index does not alter the physical order of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.
21
Q
What is Normalization and what are the advantages of it?
A
- Better database organization
- more tables with smaller rows
- efficient data access
- greater flexibility for queries
- quickly find the information
- easier to implement security
- allows easy modification
- reduction of redundant and duplicate data
- more compact database
- ensure consistent data after modification
22
Q
What is the difference between DROP and TRUNCATE commands?
A
DROP removes a table and it cannot be rolled back from the database
TRUNCATE removes all rows from the table and cannot be rolled back into the database
23
Q
Explain the different types of Normalization.
A
divides larger tables into smaller tables and links them together
1 NF
- each table cell should contain a single value
- each record needs to be unique
2 NF
- Be in 1 NF
- Single column primary key
3 NF
- Be in 2 NF
- has no transitive functional dependencies
BCNF
Stricter than 3 NF
A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
24
Q
What is ACID property in a database?
A
Atomicity
- Transactions are all or nothing
Consistency
- Only valid data is saved
Isolation
- Transactions do not affect each other
Durability
- Written data will not be lost
25
What do you mean by "Trigger" in SQL?
Trigger in SQL are a special type of stored procedies that are defined to execute automatically in plasce or after data modifications. It allows you to execute a batch of code when an insert, update, or any other query is executed against a specific table.
26
What are the different operators available in SQL?
Arithmetic
Bitwise
Comparison
Compound
Logical
27
What is the difference between cross join and natural join?
Cross join produces the cross product or Cartesian product of two tables
Natural join based on all the columns having the same name and data types in both the tables.
28
What is a subquery in SQL?
A query inside another query where a query is defined to retrieve data or information back from the database.
Subqueries are always executed first and the result of the subquery is passed on to the main query.
29
What are the different types of a subquery?
Correlated Subquery: These are queries which select the data from a table referenced in the outer query. It is not considered as an independent query as it refers to another table and refers the column in a table.
Non-correlated subquery: This query is an independent query where the output of subquery is substituted in the main query.
30
Can you list the ways to get the count of records in a table?
SELECT \* FROM table1;
SELECT COUNT(\*) FROM table1;
SELECT rows FROM sysindexes
WHERE id = OBJECT\_ID(table1) AND indid \< 2
31
Write s SQL query to find the names of employees that begin with 'A'?
SELECT \* FROM Table\_name WHERE EmpName LIKE 'A%'
32
Write a SQL query to get the third highest salary of an employee from employee\_table?
SELECT TOP 1 salary
FROM (
SELECT TOP 3 salary
FROM employee\_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
33
What is the need for group functions in SQL?
Group functions work on the set of rows and returns one result per group.
Some of the commonly used group functions are:
AVG
COUNT
MAX
MIN
SUM
VARIANCE
34
What is a Relationship and what are the different type of relationships?
Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database.
One to One
Many to One
One to Many
Self-Referencing
35
How can you insert NULL values in a column while inserting the data?
Implicitly by omitting column from column list
Explicitly by specifying NULL keyword in the VALUES clause
36
What is the main difference between 'BETWEEN' and 'IN' condition operators?
BETWEEN: Used to display rows based on a range of values in a row.
SELECT \* FROM Students where ROLL\_NO BETWEEN 10 AND 50
IN: Used to cdheck for values contained in a specific set of values.
SELECT \* FROM students WHERE ROLL\_NO IN (8,15,25);
37
Why are SQL functions used?
SQL functions are used for the following purposes.
(calculate, convery, format, modify, and manipulate)
To perform some calculations on the data
To convert the data types
To format dates and numbers
To modify individual data items
To manipulate the output
38
What is the need of MERGE statement?
Allows conditional update or insertion of data into a table.
It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
39
What do you mean by recursive stored procedure?
Recursive stored procedure refers to a stored procedure which calls by itself uintil it reaches some boundary condition.
This recursive function or procedure helps the programmers to use the same set of code n number of times.
40
What is a CLAUSE in SQL?
SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.
Example:
WHERE & HAVING clause
41
What is the difference between 'HAVING' clause and a 'WHERE' clause?
HAVING clause: Can be used only with SELECT statement. It is usually used in a GROUP BY clause.
WHERE clause: applied to each row before they are a part of the GROUP BY function in a query.
42
List the ways in which Dynamic SQL can be executed
1. Write a query with parameters
2. Using EXEC
3. Using sp\_executesql
43
What does a constraint do?
Constrants are the representation of a column to enforce data entity and consistency.
44
What are the various levels of constraints?
1. Column Level Constraint
2. Table Level Constraint
45
How can you fetch common records from two tables?
INTERSECT
SELECT column1, column2 ...
FROM table\_names
WHERE condition
INTERSECT
SELECT column1, column2 ...
FROM table\_names
WHERE condition
Example
SELECT studentID FROM student
INTERSECT
SELECT StudentID FROM Exam
46
List some manipulation functions in SQL
LOWER('string')
UPPER('string')
this function returns the string with the first letter in uppercase and the rest of the letters in lowercase.
INITCAP('string')
47
What are the different set operators available in SQL?
UNION
Combines rows from both the queries
INTERSECT
Keeps only those rows which are common in both queries
MINUS
Keeps rows from the left query which are not included in the right query
48
What is an ALIAS command?
Use the AS keyword in conjunction with the WHERE clause
SELECT emp.empID, dept.Result FROM employee emp, department AS dept WHERE emp.empID=dept.empID
49
What are aggregate and scalar functions?
Aggregate functions: used to evaluate mathematical calculation and returns a single value. These calculations are done from the column in a table.
Example: MAX(), COUNT()
Scalar functions return a single value based on the input value.
Example: UCASE(), NOW()
50
How can you fetch alternate records from a table?
You can fetch alternate records i.e. both odd and even row numbers.
Example: to display even numbers, use the following command:
SELECT studentId FROM (SELECT rowno, studentId FROM student) where mod(rowno, 2)=0
Now, to display odd numbers:
SELECT studentId FROM (SELECT rowno, studentId FROM student) WHERE mod(rowno,2)=1
51
Name the operator which is used in the query for pattern matching?
LIKE operator is used for pattern matching.
% - it matches zero or more characters.
Example:
SELECT \* FROM students WHERE studentname LIKE 'a%'
\_ - It matches exactly one character.
Example:
SELECT \* FROM student WHERE studentname LIKE 'abc\_'
52
How can you select unique records from a table?
You can select unique records from a table by using the DISTINCT keyword.
Example:
SELECT DISTINCT studentID FROM Student;
53
How can you fet6ch first 5 characters of the string?
Example:
SELECT SUBSTRING(StudentName,1,5) AS StudentName FROM student
SELECT RIGHT(StudentName,5) AS studentName FROM student
54
What is the main difference between SQL and PL/SQL?
SQL is a query language that allows you to issue a single query or execute a single INSERT/UPDATE/DELETE
PL/SQL is Oracle's "Procedural Language" SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as SELECT/INSERT/UPDATE/DELETE
55
What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.
56
What are Views used for?
A view refers to a logical snapshot based on a table or another view.
* Restricting access to data
* Making complex queries simple
* Ensuring data independence
* Providing different views of some data
57
What is a stored procedure?
A function which consists of many SQL statements to access the database system.
Several SQL statements are consolidated into a stored procedure and are executed whenever and wherever required.
This saves time and avoid writing code again and again.
58
List some advantages and disadvantages of stored procedures.
Advantage: A stored procedure can be used as a modular programming which means create one, store, and call for several times whenever it is requred. This suppiorts faster execution. It also reduces network traffic and provides better security to the data.
Disadvantages: can be executed only in the database and utilizes more memory in the database server.
59
List all the types of user-defined functions.
Scalar functions
Multi-statement valued functions
Inline Table-valued functions
60
What do you mean by collation?
Collation is defined as a set of rules that determine how data can be sorted as well as compared.
Character data is storted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width, etc.
61
What are Local and Global variables?
Local variables: these variables can be used or exist only inside the function. These variables are not used or referred by any other function.
Global variables: these variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called.
62
What is auto Increment in SQL?
Allows the user to create a unique number to get generated whenever a new record is inserted into the table.
This keyword is usually required whenever PRIMARY KEY is used.
AUTO INCREMENT keyword can be used in Oracle and IDENTIFY keyword can be used in SQL SERVER.
63
What is a Datawarehouse?
Datawarehouse refers to a central repository of data where the data is assembled from multiple sources of information.
Those data are consolidated, transformed and made available for the mining as well as online processing.
Warehouse data also has a subset of data called Data Marts.
64
What are the different authentication mdoes in SQL Server? How can it be changed?
Windows Mode | Mixed Mode
1. Click Start -\> Programs -\> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
2. Select the server from the Tools menu.
3. Select SQL Server Conficuration Properties, and choose the Security page.
65
What are STUFF and REPLACE function?
STUFF function:
This function is used to overwrite exising character or inserts a string into another string.
STUFF(string\_expression, start, length, replacement\_characters)
REPLACE function:
This function is used to replace the existing characters of all the occurrances.
REPLACE(string\_expression, search\_string, replacement\_string)
66
SQL Interview Questions
flashcards
Decks in class (1)
# Cards
