An Overview of SQL
SQL, or Structured Query Language, is a language that is used to interact or communicate with a database. This language assists in performing tasks like retrieval, insertion, updation, and deletion of data from databases. An ANSI (American National Standards Institute) standard, SQL helps developers execute queries, insert records in tables, update records, create databases, create tables, or delete tables. This information is commonly asked in SQL interview questions.
No doubt other programming languages such as ASP, PHP, and ColdFusion helped in making the Internet very dynamic, but SQL has revolutionized how users interact with websites in general. Any reputed webpage that allows the user to provide content uses SQL. This information is critical for Oracle SQL interview questions.
Development History of SQL
Dr. Edgar F. " Ted" Codd, known as the “ father of relational databases” first described a relational model for databases in 1970. In 1974, SQL appeared. If you are preparing for interviews, you can check SQL interview questions and answers queries for in-depth insight. Trusting Codd’s suggestions, IBM released System/R in 1978. Later in 1986, IBM announced the prototype of the relational database, which was ANSI standardized. This first relational database later became famous by the name of Oracle.
SQL:2016 is the updated version released in December 2016. Get more knowledge on this if you are preparing for SQL interview questions and answers for experienced professionals.
SQL Interview Questions and answers
Looking for a new job? Do not miss to read our SQL Interview Questions and answers. Whether you are a fresher or an experienced, these questions and answers that can help you to crack your interview.
- What is SQL and explain its components?
- What are the advantages of SQL? Explain
- Who introduced SQL?
- What is a Database? Explain
- Explain table and field in SQL?
- Explain the types of joins in SQL?
- What is a Primary key? Explain
- Explain Constraints in SQL?
- Explain the difference between SQL and MySQL.
- Explain Unique key in SQL.
- Explain Foreign key in SQL?
- Write a query to display the current date in SQL?
- Explain Normalization and what are the advantages of it?
- Explain the difference between DROP and TRUNCATE commands in SQL?
- What is Trigger in SQL? Explain
- Write a query to find the names of users that begin with "um" in SQL?
- Write a query to find the 3rd highest salary of an employee from the employee table in SQL?
- Explain CLAUSE in SQL?
- What is the difference between HAVING and a WHERE in SQL?
- Explain ALIAS in SQL?
- How to use distinct and count in SQL query? Explain
- Explain Aggregate functions are available there in SQL?
- Explain Scalar functions in SQL?
- What has stored procedures in SQL and how we can use it?
- How to make a copy values from one column to another in SQL?
SQL is a standard language which is used to accessing and manipulating databases. It stands for Structured Query Language. It can be used to develop a web application for a server-side scripting language, like ASP, PHP, etc.
It consists of three components that are listed below:-
- Data Definition Language.
- Data Manipulation Language.
- Data Control Language.
There are many advantages of SQL, and some of them are mentioned below:
- No coding needed: It is easy to manage without any need to write the substantial amount of code
- Well defined standards: Long established are used by the SQL databases that are being used by ISO and ANSI.
- Portability: We can use this program in PCs, servers, laptops and mobile phones.
- Interactive Language
- Multiple data views
Table: It is a collection of related data that consists of rows and columns. It has a specified number of columns but can have any number of rows.
Field: It is a column in a table that is designed to maintain specific information about all records in the table.
In SQL, Joins is used to merge records from two or more tables in a database, based on a related column between them.
Here are the four types of the joins in SQL
- INNER JOIN: It returns records that have matching values in both tables
- LEFT JOIN: It returns all records from the left table and the matched records from the right table
- RIGHT JOIN: Right Join returns all records from the right table and the matched records from the left table
- FULL JOIN: It returns all records when there is a match in either left or right table
It is a rule used to limit the type of data that can insert into a table, to maintain the integrity and accuracy of the data inside the table.
It can be divided into two types.
- Column level constraints
- Table-level constraints
These are the most common constraints that can be applied to a table.
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY etc
- Mysql is available free because it is open source, but SQL is not an open source.
- Mysql offers updateable views, but SQL offers indexed views which are more powerful.
- Mysql does not support XML, but SQL supports XML.
- Mysql not supported Auto tuning but SQL can.
- Mysql did not support User-defined functions, but SQL supported.
- Mysql support Transaction is very much limited but SQL support extensively and entirely offered.
- Mysql not offered Stored procedures and full joins but offered in SQL.
- Mysql does not support Cursor feature but is there in SQL
It is a systematic approach of decomposing tables to remove data redundancy and undesirable characteristics like Insertion, Update and Deletion. Normalization is a multi-step process that put data into the tabular form and removing duplicated data from the tables.
- It helps to the reduction of redundant data.
- It has a much more flexible database design.
- It is better to handle on database security.
- It is dealing with data consistency within the database.
Rule of Normalization
- 1NF(First Normal Form)
- 2NF(Second Normal Form)
- 3NF(Third Normal Form)
- BCNF(Boyce and Codd Normal Form)
- 4NF(Fourth Normal Form)
- It removes all rows from a table.
- It does not require a WHERE clause.
- Truncate cannot be used with indexed views.
- It is performance wise faster.
- It removes a table from the database.
- All table's rows, indexes, and privileges will also be removed when we used this command.
- The operation cannot be rolled back.
You can download here sql interview questions pdf after registeration or subscribe.
In SQL, a trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. It can be performed when you run one of the following SQL like INSERT, UPDATE and DELETE occurred in a table. It's activation time can be BEFORE or AFTER.
SELECT * FROM employee WHERE name LIKE 'um%';
SELECT salary FROM employee e1 WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM employee e2 WHERE e2.salary > e1.salary)
Our updated SQL interview questions queries for freshers are the best online resource to help you prepare for the upcoming interviews.
It is the source of a rowset to be operated upon in a DML statement. These clauses are very common which is used with Select statement, Update statement and Delete statement.
SQL provides with the following clauses that are given below:
- GROUP BY
- ORDER BY etc
WHERE clause is used for filtering rows, and it applies to every row but HAVING term is used to filter groups.
WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.
It is used to rename a table in a specific SQL statement. Renaming is a temporary change without change actual table name in the database. It can be applied to tables as well as fields.
The basic syntax of a table alias
SELECT field1, field2 FROM TABLE_NAME AS TABLE WHERE "WRITE_YOUT_CONDITION";
The basic syntax of a column alias
SELECT field1 AS column1 FROM TABLE_NAME WHERE "WRITE_YOUT_CONDITION";
count(): It returns the number of rows in a table satisfying the condition specified in the WHERE clause. It would return 0 if there were no matching conditions.
SELECT COUNT(*) FROM category WHERE 'status' = 1;
distinct(): It is used to return only different values. In a table, a column often contains many duplicate values, and sometimes we want to list the different values. It can be used with aggregates functions.
SELECT DISTINCT class FROM College ORDER BY department
This information was frequently asked during SQL interview questions for testers.
The aggregate function performs a calculation on a set of values, and it returns a single value as output. It ignores NULL values when it performs calculation except for the COUNT function.
SQL provides many aggregate functions that are listed below.
- MAX() etc
It is a set of precompiled SQL statements that are used to perform a particular task. A procedure has a name, a parameter list, and SQL statement, etc. It will help in reduce network traffic and increase the performance.
CREATE PROCEDURE simple_function AS SELECT first_name, last_name FROM employee; EXEC simple_function;
UPDATE `table_name` SET new_field=old_field