This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
MINUS – returns all distinct rows selected by the first query but not by the second.
UNION – returns all distinct rows selected by either query
UNION ALL – returns all rows selected by either query, including all duplicates.
INTERSECT – returns all distinct rows selected by both queries.
It’s an 18 character long pseudo column attached with each row of a table.
The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.
To display name of the employees that begin with ‘A’, type in the below command:
1. SELECT * FROM Table_name WHERE EmpName like 'A%'
A NULL value is not at all same as that of zero or a blank space. NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character.
Collation refers to a set of rules that determine how data is sorted and compared. Rules defining the correct character sequence are used to sort the character data. It incorporates options for specifying case-sensitivity, accent marks, kana character types and character width. Below are the different types of collation sensitivity:
Case sensitivity: A and a are treated differently.
Accent sensitivity: a and á are treated differently.
Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently.
Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.
The TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables. According to the ANSI standard, the following are the different types of joins used in SQL:
INNER JOIN
SELF JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
There are three operators available in SQL, namely:
1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators
Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this technology, you can opt for structured training from edureka!
There are three types of index in SQL namely:
Unique Index:
This index does not allow the field to have duplicate values if the column is unique indexed. If a primary key is defined, a unique index can be applied automatically.
Clustered Index:
This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.
Non-Clustered Index:
Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data. Each table can have many nonclustered indexes.
Denormalization refers to a technique which is used to access data from higher to lower forms of a database. It helps the database managers to increase the performance of the entire infrastructure as it introduces redundancy into a table. It adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.
In SQL, there is a built-in function called GetDate() which helps to return the current timestamp/date.
The differences between the clustered and non clustered index in SQL are :
1. Clustered index is used for easy retrieval of data from the database and its faster whereas reading from non clustered index is relatively slower.
2. Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
3. One table can only have one clustered index whereas it can have many non clustered index.
1. ROWCOUNT function
2. Set rowcount 3
3. Select * from employee order by empid desc Set rowcount 0
COMMIT saves all changes made by DML statements.
To add another column in the table, use the following command:
ALTER TABLE table_name ADD (column_name);
SQL stands for Structured Query Language.
Database Black Box testing involves:
Data Mapping
Data stored and retrieved
Use of Black Box testing techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)
The white box test method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following:
The white box test method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following:
As the coding error can be detected by testing the white box, it can eliminate internal errors.
To check for the consistency of the database, it selects the default table values.
This method verifies the referential integrity rule.
It helps perform the module testing of database functions, triggers, views, and SQL queries.
The black box test method generally involves interface testing, followed by database integration. It includes:
Mapping details
Verification of the incoming data
Verification of the outgoing data from the other query functions
OLTP stands for Online Transaction Processing. And is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency.
The relationship can be defined as the connection between more than one table in the database.
There are 4 types of relationships:
1. One to One Relationship
2. Many to One Relationship
3. Many to Many Relationship
4. One to Many Relationship
TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it also cannot be retrieved back.
Query within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query.
SQL first appeared in 1974. It is one of the most used languages for maintaining the relational database. In 1986, SQL became the standard of the American National Standards Institute (ANSI) and ISO (International Organization for Standardization) in 1987.
We have four types of SQL Queries:
1. DDL (Data Definition Language): the creation of objects
2. DML (Data Manipulation Language): manipulation of data
3. DCL (Data Control Language): assignment and removal of permissions
4. TCL (Transaction Control Language): saving and restoring changes to a database
An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields. A table alias is also called a correlation name .
An alias is represented explicitly by the AS keyword but in some cases the same can be performed without it as well. Nevertheless, using the AS keyword is always a good practice.
SELECT A.emp_name AS "Employee" /* Alias using AS keyword */
B.emp_name AS "Supervisor"
FROM employee A, employee B /* Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;
When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.
The update command comes under the DML(Data Manipulation Langauge) part of sql and is used to update the existing data in the table.
UPDATE employees
SET last_name=‘Cohen’
WHERE employee_id=101;
With this update command, I am changing the last name of the employee.
SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database in a way that once it is executed, the database is exposed to an attacker for the attack. This technique is usually used for attacking data-driven applications to have access to sensitive data and perform administrative tasks on databases.
For Example,
SELECT column_name(s) FROM table_name WHERE condition;
SQL stands for Structured Query Language and it is not something you can install. To implement sql queries, you would need a relational database management system. There are different varieties of relational database management systems such as:
ORACLE
MYSQL
SQL Server
ETL stands for Extract, Transform and Load. It is a three step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into tidy format, which will come in the second phase. Finally, we would have to load this tidy data into tools which would help us to find insights.
The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands like insert, update, and delete are given.
The syntax used to generate the trigger function is:
CREATE TRIGGER trigger_name
SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.
SELECT * FROM myDB.students;
Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG() and VARIANCE() are some of the most widely used group functions.
A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.
SQL CREATE and REPLACE can be used for updating the view.
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Execute the below query to update the created view.
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Entities: Entity can be a person, place, thing, or any identifiable object for which data can be stored in a database.
For example: In a company’s database, employees, projects, salaries, etc can be referred to as entities.
Relationships: Relationships between entities can be referred to as the connection between two tables or entities.
For example: In a college database, the student entity and department entities are associated with each other.
That is all in the section of Basic SQL practice questions. Let’s move on to the next section of SQL intermediate interview questions.
A View can be defined as a virtual table that contains rows and columns with fields from one or more tables.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
The key which can accept only the null value and cannot accept the duplicate values is called Unique Key. The role of the unique key is to make sure that each column and row are unique.
The syntax will be the same as the Primary key. So, the query using a Unique Key for the Employee table will be:
//
CREATE TABLE Employee (
ID int NOT NULL,
Employee_name varchar(255) NOT NULL,
Employee_designation varchar(255),
Employee_Age int,
UNIQUE(ID)
);
SQL Aggregate functions determine and calculate values from multiple columns in a table and return a single value.
There are 7 aggregate functions in SQL:
AVG(): Returns the average value from specified columns.
COUNT(): Returns number of table rows.
MAX(): Returns the largest value among the records.
MIN(): Returns smallest value among the records.
SUM(): Returns the sum of specified column values.
FIRST(): Returns the first value.
LAST(): Returns last value.
Properties of the transaction are known as ACID properties. These are:
Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully or not. If not, then the transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes are undone.
Consistency: Ensures that all changes made through successful transactions are reflected properly on the database.
Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on others.
Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure.
An organized data in the form of rows and columns is said to be a table. Here rows and columns are referred to as tuples and attributes.
And the number of columns in a table is referred to as a field. In the record, fields represent the characteristics and attributes.
Database Management System is classified into four types:
1. Hierarchical database: It is a tree-like structure where the data is stored in a hierarchical format. In this database, the parent may have many children but a child should have a single parent.
2. Network database: It is presented as a graph that allows many-to-many relationships. This database allows children to have multiple children.
3. Relational database: A relational database is represented as a table. The values in the columns and rows are related to each other. It is the most widely used database because it is easy to use.
4. Object-Oriented database: The data values and operations are stored as objects in this database. All these objects have multiple relationships between them.
The main significant subsets of SQL are:
1. DDL(Data Definition Language)
2. DML(Data Manipulation Language)
3. DCL(Data Control Language)
4. TCL(Transaction Control Language)
PL SQL stands for Procedural language constructs for Structured Query Language. PL SQL was introduced by Oracle to overcome the limitations of plain sql. So, pl sql adds in procedural language approach to the plain vanilla sql.
We need to understand what is DBMS and RDBMS. DBMS stands for Database Management System. When we have a huge database with us, we would need a proper management system which would help us organise this database. There are 4 types of database management systems:
Hierarchical
Network
Relational
Object-Oriented.
Out of these database management systems, SQL Server comes under the category of Relational database management system. A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easier to locate and access specific values within the database. It is “relational†because the values within each table are related to each other. The relational structure makes it possible to run queries across multiple tables at once.
SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.
Structured Query Language SQL is a database tool that is used to create and access the database to support software applications.It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.
A Database Management System (DBMS) is a program that controls creation, insert ,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.