SQL stands for a structured query language, meaning a domain-specific programming language for controlling data in DBMS. SQL programming skills are incredibly e beneficial and required in the market as there is extensive use of database management systems in nearly every software application. Below is a curated list of SQL interview questions and answers for experienced personals that are likely to be asked during an interview.
A stored procedure is a developed SQL code that could be saved and reused. We can also consider a stored procedure to be a function containing many SQL statements for accessing the database system. We can combine several SQL statements into a stored procedure and use them whenever required.
The stored procedure could be used as a means of modular programming, indicating we can create a stored system once and call at various times as required. Faster execution while compared to executed multiple queries is also supported.
CREATE PROCEDURE procedure_name AS Sql_statement GO; To execute, we will use this: EXEC procedure_name
We will set up a saved protocol that will allow workers to extract their age.
create procedure employee_age
select e_age from employee
Now, we will execute it.
The Join clause is used for merging rows on a linked column from two or more columns. Different forms of joins could be used to gather data that rely on the relationship of tables.
There are four types of Joins:
A temporary table lets us store intermediate outcomes and process those results. Temporary tables are created and can be discarded directly if not used again. They are accommodating when provisional data must be stored.
CREATE TABLE #table_name();
The below query will create a temporary table:
create table #book(b_id int, b_cost int)
Now, we will insert the records.
insert into #book values(1,100) insert into #book values(2,232) select * from #book
OLTP: It stands merely for on-line transaction processing and can be viewed as a powerful and transaction-oriented applications category. The ability to keep the system stable is one of the essential qualities of the OLTP system.
OLAP: OLAP is a type of application that is a relatively lower frequency for internet transactions and reflects online analytical processing. Effectiveness computing is heavily dependent on reaction time for OLAP systems. Therefore, these structures are commonly used in data recovery or for the maintenance of historical aggregates, typically using them in multi-dimensional schemes.
The evaluation approach for white boxes primarily concerns the internal configuration of a given dataset, in which users hide parameters. The procedure for checking the white box includes:
The black box test method typically consists of configuration checking and the incorporation of databases. It consists of:
The intersect operator will merge multiple selected statements and only returns the combination of a specific record of two chosen words. When Table A and Table B are over, and the Intersect operator is added to both tables, we only obtain the records typical due to chosen statements from both.
SELECT column_list FROM table1 INTERSECT SELECT column_list FROM table2
The COALESCE function takes the first non-null value in a series of inputs.
SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)
In SQL, AUTOINCREMENT is used for creating a single number automatically until a new record is placed into a table.
For each record, the primary key is unique, and we add the primary AUTOINCREMENT field to raise it by inserting a new record.
By default, the value of AUTO-INCREMENT begins at one and is raised by one until a new record is added.
CREATE TABLE Employee( Employee_id int NOT NULL AUTO-INCREMENT, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255) Age int, PRIMARY KEY (Employee_id) )
Two approaches used in databases are primarily normalization and denormalization.
Normalization is used in reducing data duplication and indebtedness by coordinating fields and tables in datasets. It includes building tables and creating relationships according to some rules between individual tables. These guidelines will eliminate complexity and contradictory reliance to make it more flexible.
Denormalization is the opposite of normalization. We add redundant data here, essentially, so that complicated queries containing many tables can be speeded up. Herein, by inserting redundant information or grouping information, we aim towards improving the read output of a database.
A Self-Join is a regular join in which a table is joined with itself depending upon some relation between its columns. It uses the inner join or left join clause, and a table is used for assigning different names to the table within the query.
SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee", B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor" FROM employee A, employee B WHERE A.emp_sup = B.emp_id;