Day 1 – SQL Server Interview Questions and Answers
What is SQL?
SQL is the internationally recognized standard language for dealing with data in relational database.
SQL is a language specifically and solely designed to create, operate on, and manage relational database.
SQL isn’t a general-purpose language, such as C++ or Java; it is Structured Query Language which is used for storing data, manipulating, and retrieving data stored in a relational database.
SQL is a language specifically and solely designed to create, operate on, and managed relational databases.
What is RDBMS?
RDBMS stands for Relational Database Management System; it is a database management system that is based on the relational model as introduced by E. F. Codd.
What are the Codd’s rules for RDBMS?
Dr. E. F. Codd defines 13 rules , oddly enough referred 12 Rules, for the Relational Model:
- A relational DBMS must be able to manage databases entirely through its relational capabilities.
- Information rule: All information in a relational database (including table and column names) is represented explicitly as values in tables.
- Guaranteed access: Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name.
- Systematic null value support: The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.
- Active, online relational catalog: The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language.
- Comprehensive data sublanguage: At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.
- View updating rule: All views that are theoretically updatable can be updated through the system.
- Set-level insertion, update, and deletion: The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes.
- Physical data independence: Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are.
- Logical data independence: Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table.
- Integrity independence: The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be.
- Distribution independence: Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.
- No subversion: It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages.
What is Table?
The table is a basic building block of database uniquely identified by its name and consists of rows that contain the stored information.
What is Tuple?
The data stored in rows of a table is known as Tuple (or record).
What do you mean by Data Integrity?
The Data Integrity contains the following categories exist with each RDBMS:
- Entity Integrity: There are no duplicate rows in a table.
- Domain Integrity: Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of values.
- Referential Integrity: According to Referential Integrity Any Row cannot be deleted, which are used by other records.
- User-Defined Integrity: It tells some specific business rules that don’t fall into entity, domain or referential integrity.
What is Database Normalization?
Database normalization is the process of efficiently organizing data in a database to avoid data redundancy. Database normalization commonly used in the form of normal forms.
How many types of Normal forms?
There are following four normal forms commonly used:
- First Normal form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (NF)
- Boyce & Codd normal forms (BCNF)
Types of Keys in SQL:
There following types of keys in SQL this is used to fetch records from tables and to make relationship among the different tables or views of the database.
- Super Key: Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.
Ex. Primary Key, Unique Key, Alternate Key are subset of Super Key.
- Candidate Key: A Candidate Key is a set of one or more fields / columns that can identify a record uniquely in a table.
- There can be multiple Candidate keys in one table.
- Each Candidate key can work as primary key.
- Primary Key: Primary key is a set on one or more fields /columns of a table that uniquely identify a record in database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.
- Alternate key: Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently in not primary key.
- Composite/Compound Key: Composite key is a combination of more than one fields/columns of a table. It can be a candidate key, Primary key.
- Unique Key: Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it cannot have duplicate values.
- Foreign Key: Foreign key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
For example: we have two table one is Employ Table and another is Department Table.