SQL Technical Interview Questions

What is SQL?

SQL stands for Structured Query Language. It is primarily used to communicate with a database. Specifically, a domain-specific language that is used in programming and designed for managing data held in a relational database management system (RDBMS). It is also used for stream processing in a relational data stream management system (RDSMS).

SQL can be used to modify database tables and index structures, as well as for adding, updating and deleting rows of data. It can also be used for retrieving subsets of information from within a database for transaction processing and analytics applications.

Since the 1970s and 1980s, SQL has become the standard programming language for relational databases.

What is a Database?

A database is nothing more than a base for data. Basically, it is a collection of data. A database is designed as such so that the data in it can be easily accessed, managed, organized, and updated. It is a system that is designed for organization and to make data management easier.

A database can be of many types depending on the type of content, such as bibliographic, full-text, numeric, and images. Or even according to their organizational approach. For example: a relational database that is a type of  tabular database in which the data is defined in such a way so that it can be reorganized and accessed in a number of different ways.

When did SQL appear?

SQL is a type of database language that is primarily used for managing a database. It can be used to create a database, delete it, fetch rows or modify them, and so much more.

SQL first appeared on the scene in 1974. Since then, it has gone on to become the standard programming language for relational databases.

What are the usages of SQL?

SQL has become the standard database language for use with databases. It can be used for the following tasks:

  • To create new databases
  • To execute queries against a database
  • To retrieve data from a database
  • To inserts records in a database
  • To updates records in a database
  • To delete records from a database
  • To create new tables in a database
  • To create views in a database
  • To delete databases
Does SQL support programming?

No, SQL does not support programming. This is primarily because it does not have a loop or Conditional statement. SQL is primarily used like a commanding language to access and modify databases.

What are the subsets of SQL?

SQL has three subsets. Each one serves a different purpose. They are as follows:

  • Data definition language (DDL) – can be used to manage table and index structure
  • Data manipulation language (DML) – can be used to add, update and delete data
  • Data control language (DCL) – can be used to authorize users to access and manipulate data
What is data definition language?

Data definition language (DDL) is a type of a computer language. Technically, it is a syntax similar to a computer programming language for defining data structures. It is primarily used to define the different structures in a database, including views, schemas, tables, indexes, etc.

DDL statements can be used to create, modify, and remove database objects such as tables, indexes, and users. Some of the common DDL statements are CREATE, ALTER, and DROP.

Data definition language is also known as data description language is some instances as it describes the fields and records in a database table.

What is data manipulation language?

Data manipulation language (DML) is a family of syntax elements that users can use to manipulate data in a database. It is similar to computer programming language.

DML is primarily used for selecting, inserting, deleting and updating data in a database. DML is also at times used to perform read-only queries of data.

A popular example of DML is SQL, which can be used to retrieve and manipulate data in a relational database. Some other forms of DML are those used by IMS/DLI, CODASYL databases, such as IDMS and others.

What is data control language?

Data control language (DCL) is a type of syntax that is similar to a computer programming language. It is primarily used to control access to data stored in a database. It is a component of SQL.

DCL is primarily used to control privilege in a Database. It defines the tasks that a user is allowed to perform, such as creating tables, sequences or views. Privileges can be of two types: System Privileges: allow the creating of a session, table, etc.; and Object Privileges: any command or query that work on tables.

DCL is used in collaboration with Data definition language (DDL) and the Data manipulation language (DML). It may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.

Two common examples of DCL include GRANT which allows specified users to perform specified tasks, and REVOKE which cancels previously granted or denied permissions.

Compare SQL and PL/SQL

SQL Stands for Structured Query Language, while PL/SQL stands for Programming Language SQL. SQL is a domain-specific language created by Oracle. It is primarily used for managing data held in a relational database management system. PL/SQL, on the other hand, is a procedural extension for SQL created by Oracle. It allows one to write a full program, including loops, variables, etc., and to accomplish multiple selects/inserts/updates/deletes, i.e. things that weren’t possible with SQL. Here are some of the primary differences between SQL and PL/SQL:

SQL

PL/SQL

SQL Stands for Structured Query Language

PL/SQL stands for Programming Language SQL.

SQL is used to execute single query.

PL/SQL is used to execute multiple statements.

SQL tells the database what to do but not how to do. So it is declarative.

PL/SQL tells the database how to do things. So it is procedural.

SQL can be used in PL/SQL programs.

PL/SQL can’t be used in SQL statement.

SQL is used with various database systems like MySQL, SQL Server, Oracle, DB2, etc.

PL/SQL is used only with Oracle database.

SQL is a data oriented language used to select and manipulate sets of data.

PL/SQL is a procedural language used to create applications

SQL is used to write queries, DDL and DML statements.

PL/SQL is used to write program blocks, functions, procedures triggers,and packages

An example of SQL query is given below.

SELECT * FROM Customers;

An example of PL/SQL program is given below.

BEGIN

dbms_output.put_line(‘Hello Workd’);

What is BCP? When is it used?

The Bulk Copy Program (BCP), as the name suggests is used to import or export large amount of data from a database. Some uses of BCP are:

  • Export data in Bulk from a server into a data file.
  • Export data in data from a query.
  • Import data in Bulk from a file to the server.
  • Used to generate format of files.
     
What are Tables and Fields?

Tables and Fields are two common elements that are often utilized in databases. They are often utilized together. A table is composed of data that are related to each other. Tables are made up of rows and columns. Fields, on the other hand, contain pieces of data. It acts as a data structure for a piece of data. An example of field can be name, age etc. Hence, it can be said that a table is made up of a number of fields, each of which contain pieces of data.

What is a primary key?

The primary key is a column in a table that uniquely identifies each row in a database table. A primary key must contain a unique value. A column defined as primary key cannot have null values. Primary key can be defined when you create a table or later using ALTER TABLE statement.

What is a unique key?

Unique key is a column that has all unique values. It guarantees that all entries in the column will be different. Unlike the primary key, you can have many unique constraints.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.