SQL Theory

Question 1

What’s a rdms?

Answer 1

It stands for relational database management system. It’s a system that manages a relational database. A relational database is a database that uses the relational model, which stores information in the form of tables with rows and columns.

Question 2

What’s sql?

Answer 2

It stands for structured query language and is the language used in a relational database.

Question 3

What are the major enterprise relational database systems?

Answer 3

1. ms sql
2. my sql
3. oracle database
4. ibm db2

They all their own version of sql. For example:
Oracle has created the PL/SQL extension of SQL, while Microsoft SQL Server uses the Transact-SQL language.
They’re fairly similar to each other but have their own quirks and features.

Question 4

What is the difference between mySQL and NoSQL databases?

Answer 4

Nosql is not relational. It doesn’t use tables to store data but reather in json with key-value pairs. It also doesn’t have rigid schema like sql, it’s dynamic. Properties in nosql can be added on the fly.

Question 5

What’s the difference between postgreSQL and mySQL?

Answer 5

They have alot of the same features. Except that PostgreSQL is an object-relational database management system. On the other hand, MySQL uses tables as a core component .

Question 6

What are the 5 major types of SQL statements?

Answer 6

Data Definition Language (DDL): Used to define or change the database’s structure (e.g. the CREATE, ALTER, and DROP statements).

Data Manipulation Language (DML): Used to change the records present in a database (e.g. the INSERT, UPDATE, and DELETE statements).

Data Query Language (DQL): Used to query the database for information that matches the parameters of the request (e.g. the SELECT statement).

Data Control Language (DCL): Used to set privileges, roles, and permissions for different users of the database (e.g. the GRANT and REVOKE statements).

Transaction Control Language (TCL): Used to save or revert the changes made by DML statements (e.g. the COMMIT and ROLLBACK statements).

Question 7

What is the difference between a primary key, a unique key, and a foreign key?

Answer 7

The difference between primary and unique key is that a primary key is the column that uniquely identifies a table like product_id or customer_id. While a unique key is a key that can only have unique values, meaning that each record for that column can’t be the same. A foreign key is a key that’s the primary key of another table.

More formal definitions:

1) A primary key is an attribute or attributes serving as a unique identifier for each record.
2) A unique key is an attribute that must be different for each record. Unlike primary keys, there can be multiple unique keys in a table.
3) A foreign key is an attribute in one table that refers to a primary key in a different table.

Question 8

What are the different types of relationships in a relational database?

Answer 8

1. one-to-one: A relationship in which a single record of type A may be linked only to a single record of type B. For example, a student name can only be associated with one student id, and vice versa.
2. One to many: A relationship in which a single record of type A may be linked to multiple records of type B. For example, a prof may teach many classes.
3. Many to one: A relationship in which multiple records of type A may be linked to a single record of type B. Multiple courses may be taught by the same professor. This is the inverse of a one-to-many relationship.
4. Many to many: A relationship in which multiple records of type A may be linked to multiple records of type B. For example, a student may take multiple courses, and each course may be taken by multiple students.

Question 9

What are the different types of clauses in SQL?

Answer 9

SQL clauses are used to qualify a database query by restricting or altering the values that it returns.
There’s FROM, WHERE, ORDER BY, AS, DISTINCT

GROUP BY: Used to group together rows that have the same values. For example, you may wish to group together students based on their major.

HAVING: Used in combination with the GROUP BY clause. It restricts the returned values to only those that fulfill a given condition.

Question 10

What are the different types of JOIN clauses in SQL?

Answer 10

A JOIN clause combines records from multiple tables into a single table, based on the common values that they share between one or more columns.

INNER JOIN: returns all records(rows) that have at one match in each table.

LEFT JOIN: Returns all records from the left table and all matching records from the right table.

LEFT JOIN: Returns all records from the right table, and all matching records from the left table.

FULL JOIN: Returns all records that have at least one match in either table.

CROSS JOIN: Returns all possible combinations of rows from the left table and the right table. This is known as the Cartesian product.

Question 11

What is the difference between the DELETE, TRUNCATE, and DROP commands in SQL?

Answer 11

DELETE: Removes one or more record from a table depending on a condition from a where clause.

TRUNCATE: This removes all the records from a table. It’s not reversible.

DROP: Deletes an entire table

Question 12

What does a NULL value represent in SQL?

Answer 12

It’s a special signifier that represents no value. You can test whether a field has a NULL value by using the SQL operators IS NULL and IS NOT NULL.

Question 13

Write a SQL query to retrieve the records of students whose first name begins with the letter D.

Answer 13

select * from students where first_name like ‘D%’

Question 14

Write a SQL query to remove all records of students who are 21 years old.

Answer 14

delete from students where age = 21

Question 15

Write a SQL query to retrieve the first 10 records of students with ID numbers that end in 0.

Answer 15

select * from students where student_id like ‘%0’ limit 10

Question 16

What is a SQL injection, and how can you prevent it?

Answer 16

A SQL injection is a type of malicious attack on a SQL database that can expose, alter, or delete sensitive data.

SQL injections occur when a user enters SQL code in a place where an application does not expect it, such as the input field on a form. The application then unintentionally executes this code. Depending on the contents of the query, the application might return sensitive information, such as usernames and passwords, that the user should not be able to access.

You can prevent SQL injections by sanitizing and validating all user input. Avoid constructing SQL queries based on user input as much as possible.

Question 17

What are the ACID database properties?

Answer 17

The acronym ACID describes four properties that are desirable for any database: atomicity, consistency, isolation, and durability.

Atomicity: Every transaction is an “all or nothing” unit that either totally succeeds or totally fails. If one part of the transaction fails, the entire transaction must fail and the database must remain unchanged.

Consistency: The database must move from valid state to valid state, obeying all defined rules and constraints.

Isolation: Executing transactions at the same time must have the same result on the database as if they were run one after the other.

Durability: If a transaction is successfully committed to the database, then it must remain committed. This is true even if the system crashes or loses power.

Question 18

What are the goals and methods of database testing?

Answer 18

Database testing is necessary to ensure that a database adheres to the ACID properties described above.

Some of the most important database elements to test are:

Transactions: Testers should verify that the database continues to fulfill the ACID properties after every transaction.

Schema: A schema is the formal structure of a database. It describes how the data is organized and what relations exist between the records and fields. Testers should verify that the schema mapping between the front end and back end is correct. In addition, the schema should contain common-sense restrictions on the contents of a field (for example, ensuring that the ID number field is a numeric value).

Triggers: A trigger is a database event that has been configured to occur once a given condition is fulfilled or another event takes place. Testers should verify that all triggers take place as expected.

Question 19

What is normalization?

Answer 19

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

Question 20

What is Denormalization.

Answer 20

DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

Question 21

What are all the different normalizations?

Answer 21

The normal forms can be divided into 5 forms, and they are explained below:

First Normal Form (1NF):
This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

Second Normal Form (2NF):
Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

Third Normal Form (3NF):
This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

Fourth Normal Form (4NF):
Meeting all the requirements of third normal form and it should not have multi- valued dependencies

Question 22

What is an Index?

Answer 22

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Question 23

What are all the different types of indexes?

Answer 23

There are three types of indexes :
Unique Index:
This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

Clustered Index:
This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

NonClustered Index:
NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

Question 24

What is a Cursor?

Answer 24

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

Question 25

What is a stored procedure?

Answer 25

Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

Question 26

What is a trigger?

Answer 26

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.

Question 27

What is a constraint?

Answer 27

Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are.

NOT NULL.
CHECK.
DEFAULT.
UNIQUE.
PRIMARY KEY.
FOREIGN KEY.

Question 28

What is the difference between Cluster and Non-Cluster Index?

Answer 28

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.

A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

Question 29

What is user defined functions?

Answer 29

User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.

Question 30

Advantages and Disadvantages of Stored Procedure?

Answer 30

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

Question 31

What is recursive stored procedure?

Answer 31

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

Question 32

What is Union, minus and Interact commands?

Answer 32

UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.

MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.

INTERSECT operator is used to return rows returned by both the queries.