SQL Flashcards
(30 cards)
WHAT IS THE DIFFERENCE BETWEEN DBMS AND RDBMS?
- DBMS stores data as file. RDBMS stores data in TABULAR form.
- No relationship between
data.
Data is stored in the form of tables
which are RELATED to each other.
Eg: Foreign key relationship. - Normalization is not present. NORMALIZATION is present.
- It deals with small quantity of
data. It deals with LARGE amount of data. - Examples: XML Examples: MySQL, PostgreSQL, SQL
Server, Oracle etc
WHAT IS A CONSTRAINT IN SQL? WHAT ARE ITS TYPES.
SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table. Constraints can be divided into the following two types, Column level constraints: Limits only column data. Table level constraints: Limits whole table data.
Types of constraints:
Not Null Constraint.
Check Constraint.
Default Constraint.
Unique Constraint.
Primary Constraint.
Foreign Constraint.
WHAT IS THE DIFFERENCE BETWEEN PRIMARY KEY AND UNIQUE KEY?
Basic:
The primary key is accepted as a unique or sole identifier for every record in the table.
When the primary key is not present in the table, then the unique key is also used as a unique identifier for records
NULL:
In the case of a primary key, we cannot save NULL values.
In the case of a unique key, we can save a null value, however, only one NULL value is supported.
Purpose:
PK supports entity integrity. UK supports unique data.
Index:
The primary key tends to generate a clustered index by default. The unique key tends to generate a non-clustered index.
Number of Key:
Each table holds just one primary key. A table can hold more than one unique key.
Value Modification:
With the primary key, we cannot modify or delete the values.
With the unique key, we can modify the column values.
Uses:
PK is used to recognize specific records in the table. UK anticipates storing duplicate entries in a column except for a NULL value.
Pk use to distinguish between two records if they are different records.
UK use to distinguish that the values in a column are distinguish.
WHAT ARE TRIGGERS AND TYPES OF TRIGGERS
Triggers are database object. Basically, these are a special type of stored procedure that is automatically fired/executed when a DDL or DML command statement related to the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.
Types of Triggers:
In SQL Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers, and Logon triggers.
WHAT IS A VIEW?
A view is a VIRTUAL table which consists of a subset of data contained in a table
or more than one table.
Views are not stored in memory like tables then why to use views?
Because of 2 reasons:
1. Views can be easily Indexed to improve the performance.
2. Extra security – DBA can hide the actual table names and expose views for
Read only operations.
Remember, in a view query is stored but the data is never stored like a table.
WHAT IS THE DIFFERENCE BETWEEN HAVING CLAUSE AND WHERE
CLAUSE?
- WHERE Clause is used before GROUP BY Clause.
HAVING Clause is used after GROUP BY Clause. - WHERE Clause cannot contain AGGREGATE function.
HAVING Clause can contain aggregate function
SELECT department_id,
SUM(salary) AS total_sal
FROM employee
WHERE hire_date > ‘01-JAN-2013’
GROUP BY department_id
HAVING SUM(salary) > 1000000
ORDER BY department_id;
WHAT IS SUB QUERY OR NESTED QUERY OR INNER QUERY IN SQL?
A Subquery or Inner query or a Nested query is a query within another SQL query
and embedded within the WHERE clause.
SELECT name, listed_price
FROM paintings
WHERE listed_price > (
SELECT AVG(listed_price)
FROM paintings
);
WHAT IS AUTO INCREMENT/ IDENTITY COLUMN IN SQL SERVER?
Auto-increment allows a unique number to be generated automatically when a
new record is inserted into a table.
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(255),
Age int
)
WHAT ARE JOINS IN SQL?
A join clause is used to COMBINE rows from two or more tables, based on a
related column between them
WHAT ARE THE TYPES OF JOINS IN SQL SERVER?
LEFT OUTER JOIN: As you can see in the diagram – It returns all records from
the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Returns all records from the right table, and the
matched records from the left table.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table.
(INNER) JOIN: Returns records that have matching values in both tables. Inner
join is the mostly used join in all joins
What is self join?
In SQL Server, the self-join is like a regular join, but it joins a table to itself. Similar to any other join, a self-join requires at least two tables. But instead of adding a different table to the join, you add another instance of the same table. It is a join between two copies of the same table. Self-join is mainly used for querying the hierarchical data stored in a single table.
There is no Self Join keyword. You write a normal join where both the tables involved in the join are the same.
The following is the syntax of the self-join query.
Syntax: Self-join
SELECT a.column1, b.column2
FROM table1 a, table1 b
WHERE condition;
In the self-join, you have to specify the table alias to both the instance of a table; else you will get an error.
WHAT ARE INDEXES IN SQL SERVER?
SQL Indexes are used in relational databases to retrieve data VERY FAST.
They are like indexes at the start of the BOOKS, which purpose is to find a topic
quickly
WHAT IS CLUSTERED INDEX?
A clustered index defines the order in which data is physically stored in a table.
Clustered index are similar to the Dictionary.
There is only one way to find a word so similarly there can be only one clustered
index per table possible.
NON-CLUSTERED INDEX
A non-clustered index is stored at one place and table data is stored in another
place. So, this index is not physically stored.
It is like the index of a BOOK.
A book can have multiple indexes like one at the start and one at the end
Similarly, a table can have multiple non-clustered indexes in a table.
WHAT IS THE DIFFERENCE BETWEEN CLUSTERED AND NON-CLUSTERED
INDEX?
- A clustered index defines the order in which data is physically stored in a table.
For example, Dictionary.
A non-clustered index is stored at one place and table data is stored in another
place.
For example, Book Index. - A table can have only one clustered index.
A table can have multiple non-clustered index. - Clustered index is faster.
Non-clustered index is slower
WHAT IS THE DIFFERENCE BETWEEN STORED PROCEDURE AND FUNCTIONS?
- SP may or may not return a value, but function must return a value.
- SP can have input/output parameters, but function only has input parameters.
- We can call function inside SP, but cannot call SP from a function.
- We cannot use SP in SQL statements like SELECT, INSERT, UPDATE, DELETE,
MERGE, etc, but we can use them with function.
SELECT *, dbo.fnCountry(city.long) FROM city; - We can use try-catch exception handling in SP, but we cannot do that
in function. - We can use transactions inside SP, but it is not possible in function.
–Stored Procedure
CREATE PROCEDURE proc_name
(@Ename varchar(50),
@EId int output)
AS
BEGIN
INSERT INTO Employee (EmpName) VALUES (@En
ame)
SELECT @EId= SCOPE_IDENTITY()
END
–UDF – User Defined Functions
CREATE FUNCTION function_name
(parameters) –only input parameter
RETURNS data_type AS
BEGIN
SQL statements
RETURN value
END;
WHAT IS THE DIFFERENCE BETWEEN DELETE, TRUNCATE AND DROP
COMMANDS?
To remove specific rows, use DELETE.
To remove all rows from a large table and leave the table structure, use TRUNCATE TABLE. It’s faster than DELETE.
To remove an entire table, including its structure and data, use DROP TABLE.
WHAT ARE ACID PROPERTIES?
ACID properties are used when you are handling transactions in SQL.
Atomicity: The atomicity property. It means either all the operations (insert, update, delete) inside a transaction take place or none. Or you can say, all the statements (insert, update, delete) inside a transaction are either completed or rolled back.
Consistency: This ensures database consistency. It means that whatever happens in the middle of the transaction, this will never leave your database in a half-completed state.
If the transaction is completed successfully, then it will apply all the changes to the database.
If there is an error in a transaction, then all the changes that have already been made will be rolled back automatically. It means the database will restore to its state before the transaction starts.
If there is a system failure in the middle of the transaction, all the changes already made will automatically roll back.
Isolation: Every transaction is individual, and One transaction can’t access the result of other transactions until the transaction completes. Or, you can’t perform the same operation using multiple transactions at the same time.
Durability: Once the transaction is completed, then the changes it has made to the database will be permanent. Even if there is a system failure or any abnormal changes also, this property will safeguard the committed data.
WHAT ARE ALL THE DIFFERENT FORMS OF NORMALIZATION?
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 multivalued dependencies.
WHAT ARE RELATIONSHIPS? WHAT ARE ITS TYPES?
Database Relationship is defined as the connection between the tables in a
database. There are various data basing relationships, and they are as follows:.
One to One Relationship.
One to Many Relationship.
Many to One Relationship.
Self-Referencing Relationship
WHAT IS DATA INTEGRITY?
Data Integrity defines the accuracy and consistency of data stored in a database.
It can also define integrity constraints to enforce business rules on the data when
it is entered into the application or database
HOW TO FETCH ALTERNATE RECORDS FROM A TABLE?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where
mod(rowno,2)=0
To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where
mod(rowno,2)=1
.HOW TO SELECT UNIQUE RECORDS FROM A TABLE?
Select unique records from a table by using DISTINCT keyword.
Select DISTINCT StudentID, StudentName from Student.
WHAT IS THE COMMAND USED TO FETCH FIRST 5 CHARACTERS OF THE
STRING?
There are many ways to fetch first 5 characters of the string -.
Select SUBSTRING(StudentName,1,5) as studentname from student
Select LEFT(Studentname,5) as studentname from student
