Viva Questions- SQL
1. What is DBMS?
A Database Management System (DBMS) is a program that controls creation, maintenance
and use of a database.
Advantages:
a. Reduced Data Redundancy & Inconsistency
b. Data Security
c. Data Integrity
d. Data Sharing & Concurrency
e. Efficient Data Access & Recovery
2. What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS store the data into the
collection of tables.
Example: SQL Server, MySql, Oracle
3. What is SQL?
SQL stands for Structured Query Language , and it is used to communicate with the
Database to perform tasks such as retrieval, updation, insertion and deletion of data from a
database.
4. What is a Database?
Database is an organized collection of data for easy access, storing, retrieval and managing of
data.
Example: School Management Database, Bank Management Database.
5. What are tables and Field(attribute)?
Table is a set of data that are organized in a model with Columns and Rows.
Field(attribute)-Columns in a table(realation).
Tupule/Row/Record- Rows in a table(realation).
Degree- No of Columns in a table(realation).
Cardinality- No of Rows in a table. A table has specified number of column called fields but
6. What is a primary key?
A primary key is a combination of fields which uniquely specify a row in a relation.
NOT NULL +UNIQUE=Primary key
Candidate Key: All possible combination of attributes that can serve as primary Key.
Alternate Key: It is a candidate key other than primary key.
Foreign Key: Is a non-key attribute whose values are derived from primary key of another
table(relation).
Domain: A set of all possible values for a given column.
7. What is a View?
A view is a virtual table which consists of a subset of data contained in a table.
8. What is a join?
This is a keyword used to query data from more tables based on the relationship between the
fields of the tables.
9. What is a query?
A query is a code written in order to get the information back from the database.
10. What is the difference between DELETE and TRUNCATE commands?
DELETE- DML command is used to remove rows from the table, and WHERE clause can be
used for conditional set of parameters. Commit and Rollback can be performed after delete
statement.
TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.
Drop- DDL command, which is used to permanently delete an entire database(also table),
including all its tables
11. DDL & DML
DDL (Data Definition Language) DML (Data Manipulation Language)
Defines the database schema and Manages and manipulates the data records within
structure Eg: Create, Alter , Drop the established structure.
Eg: Insert, delete ,update,..
12. What is a constraint?
Constraint are used to specify the condition on the columns in a table.
NOT NULL.
CHECK.
DEFAULT.
UNIQUE.
PRIMARY KEY.
FOREIGN KEY.
13. What is Join? Different types of join.
SQL JOIN clause is used to combine rows from two or more tables based on a related column
between them
Cross join OR Cartesian product where number of rows in the first table multiplied by
number of rows in the second table.
NATURAL JOIN in SQL automatically combines rows from two tables based on all columns that
share the same name and compatible data types
EQUI JOIN in SQL combines rows from two or more tables based exclusively on a matching condition
using the equality operator ( = )
14. What is the difference between Where & having?
WHERE Clause HAVING Clause
Filters individual rows based on specific Filters groups or aggregated results based on
conditions. summary values.
Applied before data is grouped by the GROUP Applied after data is grouped and aggregated.
BY clause.
Cannot be used with aggregate functions Can (and is typically) used with aggregate
like SUM(), AVG(), or COUNT(). functions.
Works with SELECT, UPDATE, Generally only used with SELECT statements.
and DELETE statements.
15. What are aggregate and scalar functions?
Aggregate functions are used to evaluate mathematical calculation and return single values.
Aggregate – max(), sum(),min()
16. How to select unique records from a table?
Select unique records from a table by using DISTINCT keyword.
Select DISTINCT StudentID, StudentName from Student.
17. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
1. % - Matches zero or more characters.
2. _(Underscore) – Matching exactly one character.
Example -.
Select * from Student where studentname like 'a%'
Select * from Student where studentname like 'ami_'
[Link] is the difference between char and varchar?
CHAR VARCHAR
Fixed-length string Variable-length string
Reserves the maximum defined space, even Uses only the space required for the data stored, plus a
if the actual data is shorter. small overhead (1 or 2 bytes) to record the length.