SQL

Question 1

What is a primary key?

Answer 1

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values and cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key

Question 2

What is a foreign key?

Answer 2

A foreign key is a key used to link two tables together. This is sometimes called a referencing key. Foreign key is a column or a combination of columns whose values match a Primary Key in a different table. This is to establish a relationship between records in a separate table.

Question 3

What is referential integrity?

Answer 3

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remain synchronized during updates and deletes.

Question 4

How can you retrieve rows from a database table?

Answer 4

SQL SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These tables are called result-sets.

SELECT COLUMN1, COLUMN2 FROM TABLE_NAME

Question 5

How do you add a record to a table?

Answer 5

SQL INSERT INTO statement is used to add new rows of data to a table in the database.

INSERT INTO TABLE_NAME (COLUMN1, COLUMN2) VALUES (VALUE1, VALUE2);

Question 6

What is the difference between inner join and outer join?

Answer 6

A join is used to compare and combine and return specific rows of data from two or more tables in a database.

Inner Join
An inner join focuses on the commonality between two tables. When using an inner join, there must be at least some matching data between two (or more) tables that are being compared.

SELECT COLUMN1 FROM TABLE_NAME INNER JOIN TABLE_NAME ON TABLE_NAME1.COLUMN1 = TABLE_NAME2.COLUMN1

Outer Join
An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table

Left Outer Join (Left Join)
Right Outer Join (Right Join)
Fill Outer Join (Full Join)

Question 7

What is the difference between IN and EXISTS?

Answer 7

In
Returns true if a specified value matches any value in a subquery or a list. Works best for a small finite set of data. When used and combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole.

SELECT COLUMN1 FROM TABLE_NAME WHERE COLUMN1 IN (VALUE1, VALUE2);

Exists
Return true if a subquery contains any rows. Is better for subqueries

SELECT COLUMN1 FROM TABLE_NAME WHERE COLUMN1 EXISTS (SELECT COLUMN1 FROM TABLE_NAME WHERE CONDITION);

Question 8

What is the WHERE clause?

Answer 8

The WHERE clause is used to filter records and extract only those records that fulfill a specified condition.

SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;

Question 9

What is the purpose of AND and OR?

Answer 9

The WHERE clause can be combined with AND, OR, and NOT operators to filter records based on more than one conditions

The AND operator displays a record if all the conditions separated by AND is TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.

Question 10

How do you add a column to the table?

Answer 10

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table and can also be used to add and drop various constraints in an existing table.

ALTER TABLE TABLE_NAME ADD/DROP COLUMN_NAME;
ALTER TABLE TABLE_NAME ALTER/MODIFY COLUMN_NAME DATATYPE;

Question 11

Can you have more than one primary key in one table?

Answer 11

No. A table can have only one primary key constraint.

Question 12

Can a primary key contain more than one column?

Answer 12

Yes. A primary key with more than one column is called a composite key.

Question 13

What is a one to one relationship?

Answer 13

One-to-One relationship is defined as the relationship between two tables where both tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-unique foreign key constraints

Question 14

What is a one to many relationship?

Answer 14

One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using primary key-foreign key relationship.

Question 15

What is a many to many relationship?

Answer 15

Many-to-Many relationship is defined as a relationship between two tables where many rows from one table can have multiple matching rows in another table. Neither table can support a foreign key to relate the tables, so a junction table (join table or associative entity) is created. A junction table is a database table that contains foreign key references to two or more other database tables. It is the standard way of creating a many-to-many relationship between tables

Question 16

How do you drop a table?

Answer 16

DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

DROP TABLE TABLE_NAME

Question 17

What is the difference between DELETE, TRUNCATE, and DROP?

Answer 17

DELETE

1. Removes some or all rows from a table
2. A WHERE clause can be used to remove some rows. If no Where condition is specified, all rows will be removed
3. Causes all DELETE triggers on the table to fire
4. Removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE
5. It is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.

TRUNCATE

1. Removes all rows from a table
2. No triggers are fired on this operation because it does not operate on individual rows
3. TRUNCATE is not possible when a table is reference by a foreign key.
4. It is a DDL command as it resets IDENTITY columns

DROP

1. Removes table from database
2. No DML triggers will be fired
3. Can’t be rolled back
4. DDL command

Question 18

What is an ERD?

Answer 18

Entity Relational Diagram
It is a snapshot of data structures that show entities (tables) in a database and relationships between tables within that database.

Elements

1. Entities, things for which we want to store information
2. Attributes, data that we want to collect for an entity
3. Relationship, descriptions of the relationship between entities

Question 19

What are the various constraints in SQL?

Answer 19

NOT NULL
Indicates that a column cannot store NULL value

UNIQUE
Ensures that each row for a column must have a unique value

PRIMARY KEY
A combination of a NOT NULL and UNIQUE. Ensures that a column or columns have a unique identity which helps to find a particular record in a table more easily and quickly

FOREIGN KEY
Ensure the referential integrity of the data in one table to match values in another table

CHECK
Ensures that the value in a column meets a specific condition

DEFAULT
Specifies a default value for a column

Question 20

What is the difference between WHERE and HAVING?

Answer 20

WHERE
Does not work with aggregates like SUM

HAVING
Can compare aggregates to other values

Question 21

What is normalization?

Answer 21

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. There are 6 normal forms

Question 22

What is a nested query, or a subquery?

Answer 22

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.

Question 23

What is an orphan?

Answer 23

Orphaned records are records that reference a key which no longer exists in the foreign table. If referential integrity is enforced by using a foreign key constraint, this cannot happen.

Question 24

What is the purpose of SELECT INTO?

Answer 24

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.

SELECT
c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode
INTO
dbo.EmployeeAddresses FROM Person.Person AS c JOIN HumanResources.Employee AS e ON e.BusinessEntityID = c.BusinessEntityID JOIN Person.BusinessEntityAddress AS bea ON e.BusinessEntityID = bea.BusinessEntityID JOIN Person.Address AS a ON bea.AddressID = a.AddressID JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;

Question 25

What is a cross join?

Answer 25

A cross join that does not have 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 the second table.

SELECT p.BusinessEntityID, t.Name AS Territory FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t ORDER BY p.BusinessEntityID;

Question 26

What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

Answer 26

INNER JOIN: Returns all rows when there is at least one match in BOTH tables

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

Question 27

What is a cascade delete?

Answer 27

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Oracle.

Question 28

What are some of the String manipulation methods in SQL?

Answer 28

SUBSTRING
The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set.

TRIM
TRIM enables you to trim leading or trailing characters (or both) from a character string.

LTRIM
LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, it defaults to a single blank.

RTRIM
RTRIM removes from the right end of char all of the characters that appear in set.

REVERSE
Returns the reverse order of a string value.

REPLACE
REPLACE returns char with every occurrence of search_string replaced with replacement_string.

LENGTH
The LENGTH functions return the length of char.

CONCAT
CONCAT returns char1 concatenated with char2.

UCASE

LCASE

Question 29

What is GRANT and REVOKE?

Answer 29

You can grant users various privileges to tables and revoke those privileges. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

GRANT privileges ON object TO user; REVOKE privileges ON object FROM user;

Question 30

What is the difference between Order By and Group By?

Answer 30

ORDER BY is used for changing the order in which records are returned according to specified column.

GROUP BY is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Question 31

What is a view?

Answer 31

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view. Views, which are kind of virtual tables, allow users to do the following:

Structure data in a way that users or classes of users find natural or intuitive.
Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
Summarize data from various tables which can be used to generate reports.

Question 32

What’s an index?

Answer 32

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers. An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data. Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order. Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there’s an index.

Question 33

What is a trigger?

Answer 33

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
A database definition (DDL) statement (CREATE, ALTER, or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers
Triggers can be written for the following purposes − Generating some derived column values automatically Enforcing referential integrity Event logging and storing information on table access Auditing Synchronous replication of tables Imposing security authorizations Preventing invalid transactions

CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;

Question 34

How do you prevent SQL injection?

Answer 34

Primary Defenses:
Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input

Additional Defenses:
Also Enforce: Least Privilege
Also Perform: White List Input Validation

Question 35

What is connection pooling?

Answer 35

A connection pool is a set of idle, open, and reusable database connections maintained by the database server so that the connections can be reused when the database receives future requests for data, instead of exclusively opening a new connection.

The benefit of connection pooling, is that connections placed in the pool and are used over again so that a new connection does not have to be established, reducing amount of time needed to establish connections.

Also, opening and closing of connections to the database becomes less resource-expensive in terms of CPU and memory.

Question 36

How does connection pooling work?

Answer 36

When a connection is first opened, a connection pool is created based on matching criteria that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. If the connection string is not an exact match to an existing pool when a new connection is opened, a new pool is created. Connections are pooled per process, per application domain, per connection string, and, when integrated security is used, per Windows identity.

Whenever a user calls “open” on a connection with a matched connection string, the “pooler” looks for an available connection in the pool that corresponds to that connection string. If a pooled connection is available, it returns it to the caller, otherwise it will add a new connection to the pool, up to the maximum pool size specified (100 is the default). When the application calls “close” on the connection, instead of closing the connection, the pooler returns it to the pooled set of active connections. Once the connection is returned to the pool, it is ready to be reused on the next “open” call.

When the connection is closed or disposed, it is returned to the pool and remains idle until a request for a new connection comes in. The connection pooler removes a connection from the pool after it has been idle for a long time.

Question 37

What is a scalar function?

Answer 37

SQL scalar functions return a single value, based on the input value.
UCASE() - Converts a field to upper case
LCASE() - Converts a field to lower case
MID() - Extract characters from a text field
LEN() - Returns the length of a text field
ROUND() - Rounds a numeric field to the number of decimals specified
NOW() - Returns the current system date and time FORMAT() - Formats how a field is to be displayed

Question 38

What is an aggregate function?

Answer 38

SQL aggregate functions return a single value, calculated from values in a column. 
AVG() - Returns the average value 
COUNT() - Returns the number of rows 
FIRST() - Returns the first value 
LAST() - Returns the last value 
MAX() - Returns the largest value 
MIN() - Returns the smallest value 
SUM() - Returns the sum

Question 39

What is a cursor?

Answer 39

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time.

There are two types of cursors −
Implicit cursors
Explicit cursors

Question 40

What is the context area?

Answer 40

Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc.

Question 41

What is an implicit cursor?

Answer 41

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Question 42

What is an explicit cursor?

Answer 42

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −
CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −
Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory

Question 43

List each of the ACID properties that collectively guarantee that database transactions are processed reliably.

Answer 43

ACID
Atomicity
Consistency
Isolation
Durability

Question 44

What is ACID atomicity?

Answer 44

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 goes back to its most consistent state.

Question 45

What is ACID consistency?

Answer 45

Consistency. The consistency property says that each transaction will ensure that the database move from one valid state to another.

Question 46

What is ACID isolation?

Answer 46

Isolation. The isolation property says that if multiple transaction is happening serially, one another another, it will wait first the first transaction to complete before starting the next transaction.

Question 47

What is ACID durability?

Answer 47

Durability. Durability means that once a transaction has been committed, you will not be able to do a rollback, 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.

Question 48

What is DBMS?

Answer 48

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

Question 49

What is RDBMS?

Answer 49

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.

Example: SQL Server.

Question 50

What is SQL?

Answer 50

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Standard SQL Commands are Select.

Question 51

What is a Database?

Answer 51

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Question 52

What are aggregate and scalar functions?

Answer 52

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table.

Scalar functions return a single value based on the input value.

Aggregate – max(), count – Calculated with respect to numeric.

Scalar – UCASE(), NOW() – Calculated with respect to strings.

Question 53

What are various DDL commands in SQL? Give brief description of their purposes.

Answer 53

Data Definition Language: Defines data structure

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.

Question 54

What are various DML commands in SQL? Give brief description of their purposes.

Answer 54

Data Manipulation Language: Manipulate data

SELECT − it retrieves certain records from one or more tables.

INSERT − it creates a record.

UPDATE − it modifies records.

DELETE − it deletes records.

Question 55

What are various DCL commands in SQL?

Answer 55

Following are various DCL or Data Control Language commands in SQL −

GRANT − it gives a privilege to user.

REVOKE − it takes back privileges granted from user.

Question 56

Is a NULL value same as zero or a blank space? If not then what is the difference?

Answer 56

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.

Question 57

What is PL/SQL?

Answer 57

PL/SQL is a procedural language that extends the Structured Query Language. It is made of 3 blocks, declarative, executable and exception-building part. it is possible to save these blocks as objects to be reused later. SQL can only run one statement at a time, with the block, you can put multiple statements into the block and run it as is.

Question 58

What is the 1NF (1st Normal Form)?

Answer 58

1NF

1. Contains only atomic values (Values that can’t be divided)
2. There are no repeating groups

Question 59

What is the 2NF (2nd Normal Form)?

Answer 59

2NF
It is in first normal form
All non-key attributes are fully functional dependent on the primary key

Question 60

What is the 3nd (3rd Normal Form)?

Answer 60

3NF
It is in second normal form
There is no transitive functional dependency (Where one table is dependent on the other)

Question 61

What is Union?

Answer 61

UNION
Combine the results of two or more SELECT statements without returning duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length

SELECT COLUMN1, COLUMN2 FROM TABLE_NAME UNION SELECT COLUMN1, COLUMN2 FROM TABLE_NAME

Question 62

What is UNION ALL?

Answer 62

UNION ALL
Combine the results of two or more SELECT statements including returning duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length

SELECT COLUMN1, COLUMN2 FROM TABLE_NAME UNION ALL SELECT COLUMN1, COLUMN2 FROM TABLE_NAME

Question 63

What is MINUS?

Answer 63

MINUS
The MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

Question 64

What is INTERSECT?

Answer 64

INTERSECT
The INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.