SQL refers to the structured query language, and it handles the extraction, management, and data permissions from a Relational Database Management Systems (RDBMS) like MySQL. Users must use this programming language for MySQL. It operates the MYSQL databases, creates tables, and stores data within them. Almost all database systems are compatible with SQL queries. Here, we are here with top Complex SQL Interview Questions with simple answers.
Why do we use SQL?
The American National Standards Institute (ANSI) recognizes SQL as the standard language for all RDBMS. It is a diverse language that provides several options to create, update, add, etc., the data in an RDBMS. It is capable of storing and managing large amounts of data. It is free, open-source, and easy to use in comparison to other programming languages.
Introduction Data Models & SQL
Relational Data Model
This is the most used Data model. A data model is the set of restrictions that the database follows. It focuses on the structure detail of the RDBMS. Certain features of the relational model are:
- Relation: It refers to the table in which we store all the information. A table consists of rows and columns. Rows are also known as tuples, and columns are also known as attributes.
- Primary Key: It refers to the key that allocates a unique identity to every tuple in the table. A primary key can only be one in number. For example, the roll no. column in a student management system.
- Candidate Key: All columns that can be set as primary key refer to candidate keys as candidates for the primary key.
- Degree & Cardinality: The number of rows in a table refers to the degree, and the number of columns in a table refers to the cardinality.
- Alternate Key: All keys which are not chosen to be primary key refer to alternate keys.
- Foreign Key: Get values from another relation and it is not a key attribute.
MySQL
MySQL refers to an RDBMS, which handles storing and representing data. It functions on the client & server method. The machines consisting of databases help in the functioning of the server. The clients connect to the server with the help of a network.
- MySQL Server: It takes requests from the client and accesses the database as per those. It extracts the data required and transmits that information to the client.
- Client: It sends its requests for data extraction to the server over a network. They act as the connecting factor between the server and the queries.
Classification of SQL Statements
SQL statements have distinct purposes, due to which they fall under different categories. They are as follows:
- Data Definition Language Commands: These commands are responsible for actions that define the data. They are responsible for creating, altering, dropping, and maintaining the records. For example, create, drop, truncate, alter.
- Data Manipulation Language Commands: These commands are responsible for actions like modifying & deleting, to control the data. For example, insert, update, delete, etc.
- Transaction Control Language Commands: Queries like manipulating, changing the data, undoing it, etc. fall under this category. For example, commit, rollback, savepoint, etc.
Student Management System
In this article, we have taken up the Student Management System to understand MySQL and SQL better. It covers details like the student’s personal information and stores all that data. SQL allows us to extract the required information as per our needs. It focuses on giving us an insight into SQL and the execution of its queries.
Common Datatypes Used in SQL
| DATATYPE | PROPERTIES |
| CHAR | String (0-255) |
| VARCHAR | String (0-255) |
| INT | Integer |
| DOUBLE | Decimal (24 to 53 digits) |
| FLOAT | Decimal (precise to 23 digits) |
| DECIMAL | “DOUBLE” stored as a string |
| DATE | YYYY-MM-DD |
| DATETIME | YYYY-MM-DDHH: MM: SS |
| BOOLEAN | TINYINT (1) |
Creating a Table and Basic SQL Queries
- Creating a database and using it: This is used to create a database and use it.
Syntax:
Create database <database name>;
Use <database name>;
- Creating a table: Here, we learn how to create the basic structure of the table, by using the datatypes required and specifying the limit for each of them.
The syntax to create a table is as follows:
- Insert data in the table: Here, we learn how to insert the information required within the relation.
The syntax is as follows:
Insert into student values(1,”Harry”,”Science”,”2003-06-29”,”CLASSC”,16);
Insert into student values(2,”Zayn”,”Maths”,”2002-08-19”,”CLASSA”,17);
Insert into student values(3,”Louis”,”Business”,”2002-12-13”,”CLASSD”,17);
Insert into student values(4,”Liam”,”Maths”,”2003-11-17”,”CLASSA”,16);
Insert into student values(5,”Niall”,”English”,”2003-10-23”,”CLASSC”,16);
Insert into student values(6,”Niall”,”English”,”2003-10-23”,”CLASSC,” 16);
- Displaying structure of relation and data in table: Here, we learn the query used to display the structure of the table along with the information we have inserted in the table.
The syntax for displaying the structure is as follows:
The syntax for displaying the information is as follows:
- Selecting particular rows & particular columns:
The syntax for displaying certain rows is as follows:
The syntax for displaying a certain column is as follows:
- Selecting information based on conditions:
The syntax to display the above mentioned is as follows:
Conclusion
SQL is a productive, flexible and compatible programming language. It enables database interaction, allows us to create a table in the database, retrieve data, etc. It is easy to understand and operate. It can extract a huge amount of information and is recognizable by approximately all RDBMS.
Frequently Asked Complex SQL Interview Questions
- List down each of the ACID properties and explain them in detail.
ACID stands for Atomicity, Consistency, Isolation, and Durability, making sure that all transactions specified using queries occur.
- Atomicity: It refers to that property that functions on the “all or nothing” principle. It implies that the transaction will not take place if even one of the steps is not complete.
- Consistency: It refers to that property that ensures there’s consistency throughout the transaction. It makes sure the transactions follow all constraints and remain valid.
- Isolation: It ensures that all transactions are being executed right one after the other. It displays the results one after the other and in a manner that is sequential.
- Durability: It ensures that once a transaction has taken place the transaction remains permanent. Irrespective of any circumstance, the query once executed, will be permanent.
- What is the difference between CHAR and VARCHAR?
| CHAR | VARCHAR |
| CHAR is the datatype used in cases where the user has to specify a fixed length of characters. | VARCHAR is the datatype used in cases where the user can specify a length that might vary. |
| This means that, in every column where the user specifies CHAR, all values entered will have that fixed length in the form of bytes. | This means that, in every column where the user has specified VARCHAR, all values entered will have exactly the length entered in the cell. |
| Blanks are added in places that are left unfilled, but the value of length does not change. | No blanks are added on places that are left unfilled, and the value of length varies. |
- What command is given in order to display the first 5 records in a table?
The syntax in order to display the first 5 records in a table is as follows: Select * from <table name> where <row name> <=5;
- How do you display the odd rows in a table?
The syntax in order to display the odd rows in a table is as follows: Select * from <table name> where mod(<row>,2)=1;
- What is the difference between the aggregate functions and individual functions?
| Individual Row Functions: | Aggregate Row Functions: |
| Individual row functions refer to those functions which modify one record at a time. For example- where clause | Aggregate row functions refer to those functions that collectively modify records. For example: having a clause. |
- Select all records from a table where name is “<name 1> and <name 2>.
The syntax to display the above mentioned will be as follows: Select * from <table name> where name in (<”name 1”>,<”name 2”>);
- Select all records from a table where name is not “<name 1> and <name 2>.
The syntax to display the above mentioned will be as follows: Select * from <table name> where name not in (<”name 1”>,<”name 2”>);
- How do you display the data in descending/ascending order in SQL?
The syntax to display the above mentioned will be as follows(descending order): Select * from <table name> order by <column name> desc;
The syntax to display the above mentioned will be as follows(ascending order): Select * from <table name> order by <column name> asc;
- What is the group by clause used for?
The group clause is an aggregate function used for grouping together identical values. It is also used to sort identical values.
- How do you count duplicate rows?
Select <row name>,count(<row name>) from student having count(<row name>) >1 order by count(<row name>) asc;
