SQL interview questions and answers

SQL stands for Structured Query Language and it is a programming language used to interact with databases. It is used to perform operations on data such as inserting, updating, deleting, querying, and creating databases and tables. SQL is used to retrieve data from a database and it is a powerful language that can be used to manipulate data.

Here are some examples of how SQL is used:

1. Creating databases and tables: SQL can be used to create databases and tables within those databases. This is done by using the CREATE command.

2. Inserting data: SQL can be used to insert data into a database. This is done by using the INSERT command.

3. Updating data: SQL can be used to update existing data in a database. This is done by using the UPDATE command.

4. Deleting data: SQL can be used to delete existing data in a database. This is done by using the DELETE command.

5. Querying data: SQL can be used to query data from a database. This is done by using the SELECT command.

Overall, SQL is a powerful language used to interact with databases and perform operations on data. It can be used to create, insert, update, delete, and query data from a database.

Some of the most common SQL commands are SELECT, INSERT, UPDATE, DELETE, ALTER, and CREATE.

SELECT: This is used to retrieve data from a database table.

For example, if you want to select all of the data from a table called 'Users', you would use the following command:

SELECT * FROM Users;

INSERT: This is used to add new records to a database table.

For example, if you want to insert a new user into the 'Users' table, you would use the following command:

INSERT INTO Users (Name, Email, Age) VALUES ('John Doe', 'john@example.com', 35);

UPDATE: This is used to update existing records in a database table.

For example, if you want to update the age of the user 'John Doe' in the 'Users' table, you would use the following command:

UPDATE Users SET Age = 36 WHERE Name = 'John Doe';

DELETE: This is used to delete an existing record from a database table.

For example, if you want to delete the user 'John Doe' from the 'Users' table, you would use the following command:

DELETE FROM Users WHERE Name = 'John Doe';

ALTER: This is used to modify an existing database table.

For example, if you want to add a new 'PhoneNumber' column to the 'Users' table, you would use the following command:

ALTER TABLE Users ADD COLUMN PhoneNumber VARCHAR(20);

CREATE: This is used to create a new database table.

For example, if you want to create a table called 'Products', you would use the following command:

CREATE TABLE Products (ProductId INT PRIMARY KEY, Name VARCHAR(50), Price DECIMAL);

A primary key is a column or a set of columns in a table that uniquely identify each row in the table. It is used to ensure data integrity and prevent duplicate records from being entered into the table. A foreign key is a column or set of columns that references the primary key of another table. It is used to establish a relationship between two tables.

For example, consider a Student table and a Course table. The Student table contains information about students such as their name, student ID, etc. The primary key for the Student table would be the student ID. The Course table contains information about courses such as the course name, course ID, etc. The primary key for the Course table would be the course ID. The Student table also contains a foreign key that references the Course table. This foreign key could be the Course ID, which would be linked to the primary key of the Course table. This would establish a relationship between the two tables and allow us to query the data in both tables.

To summarize, a primary key is a column or set of columns that uniquely identify each row in a table. A foreign key is a column or set of columns that references the primary key of another table and is used to establish a relationship between two tables.

To create a table in SQL, I would use the CREATE TABLE statement. This statement allows you to create a new table in a database. The syntax for creating a table is as follows:

CREATE TABLE table_name (
column_1 data_type,
column_2 data_type,
column_3 data_type
);

For example, if I wanted to create a table called "employees" with the columns "name", "age", and "salary", I would use this statement:

CREATE TABLE employees (
name VARCHAR(255),
age INT,
salary INT
);

This statement would create a table called "employees" with three columns named "name", "age", and "salary". The data type for the "name" column is VARCHAR(255), which indicates that the column can store a string of up to 255 characters. The data type for the "age" column is INT, which indicates that the column can store an integer value. The data type for the "salary" column is also INT, which indicates that the column can store an integer value.

Once the table is created, you can then insert data into the table using the INSERT statement. For example, if I wanted to insert a row into the "employees" table with the name "John Smith", age "26", and salary "50000", I would use this statement:

INSERT INTO employees (name, age, salary)
VALUES ('John Smith', 26, 50000);

This statement would insert a new row into the "employees" table with the name "John Smith", age "26", and salary "50000".

The INSERT command in SQL is used to add new data into a database table. It can be used to insert a single row or multiple rows at once.

To use the INSERT command, you must specify the table you want to insert data into, the columns of the table that the data should be added to, and the values to be inserted.

For example, if I wanted to add a new record to a table called ‘Books’, which has columns for ‘Title’, ‘Author’, and ‘Price’, the following statement would be used:

INSERT INTO Books (Title, Author, Price) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 10.99);

This statement would add a new record to the ‘Books’ table with the title ‘The Great Gatsby’, the author ‘F. Scott Fitzgerald’, and the price 10.99.

You can also use the INSERT command to add multiple records at once by using the keyword VALUES multiple times. For example:

INSERT INTO Books (Title, Author, Price)
VALUES ('The Catcher in the Rye', 'J. D. Salinger', 8.99),
('To Kill a Mockingbird', 'Harper Lee', 9.99);

This statement would add two new records to the ‘Books’ table, one for ‘The Catcher in the Rye’ and one for ‘To Kill a Mockingbird’.

You can also use the INSERT command to add data from another table. For example:

INSERT INTO Books (Title, Author, Price)
SELECT Title, Author, Price
FROM Old_Books;

This statement would add all of the records from the ‘Old_Books’ table into the ‘Books’ table.

The WHERE clause in SQL is used to filter data in a query and only return results that meet certain criteria. It is the part of a query that specifies which records should be returned.
For example, if you want to return only records that contain the word "red", you would use the WHERE clause to filter out any records that do not contain the word "red".

More specifically, the WHERE clause is used to:

• Specify criteria for selecting rows from a table.

• Compare values in one or more columns of a table.

• Combine multiple criteria in a query.

• Negate conditions using the NOT operator.

• Use the LIKE operator to search for patterns.

• Use logical operators such as AND and OR to combine multiple conditions.

For example, if you wanted to display all records in a table where the value of the "color" column is "red", you could use the following query:

SELECT *
FROM table
WHERE color = 'red';

This query would return all records where the value of the "color" column is "red".

The SELECT statement in SQL is used to query data from a database. It is used to select specific columns, rows, or entire tables from the database. The syntax of the SELECT statement is:

SELECT [column_name1,column_name2,…]
FROM [table_name]
WHERE [conditions]

For example, to query the first name of all customers in the customer table, you would write the following query:

SELECT first_name
FROM customer

The SELECT statement is also used to join multiple tables together. To do this, you need to specify the relationship between the tables and the columns they are joined on. For instance, if you wanted to join the customer and orders tables on the customer ID column, you would write the following query:

SELECT c.first_name, o.order_date
FROM customer c
INNER JOIN orders o
ON c.customer_id = o.customer_id

The SELECT statement can be used with other SQL statements such as INSERT, UPDATE, and DELETE to modify data in the database. It is an important statement used to access and manage data in a database.

Modifying existing data in a database using SQL can be done in several ways.

The most common approach is to use the UPDATE command. This command allows you to update the values of existing records in the database. The syntax of UPDATE is as follows:

UPDATE [table_name]
SET [column_name1] = [new_value1], [column_name2] = [new_value2], ...
WHERE [conditions];

For example, if there is a table called 'users' with columns 'name', 'age', and 'location', you can update the age of a user named John to 30 by using the following command:

UPDATE users
SET age = 30
WHERE name = 'John';

You can also use the INSERT command to modify existing data in a database. This command allows you to insert a new record into the database, which may contain data that updates existing records. The syntax of INSERT is as follows:

INSERT INTO [table_name] ([column_name1], [column_name2], ...)
VALUES ([value1], [value2], ...);

For example, if you want to update the location of the user named John to 'New York', you can use the following command:

INSERT INTO users (name, location)
VALUES ('John', 'New York');

Finally, you can use the DELETE command to remove existing records from the database. This command allows you to delete records that match certain conditions. The syntax of DELETE is as follows:

DELETE FROM [table_name]
WHERE [conditions];

For example, if you want to delete the record of the user named John, you can use the following command:

DELETE FROM users
WHERE name = 'John';

The UPDATE command in SQL is used to modify existing records in a database table. Specifically, it's used to change the value of one or more columns in a row of data. Here are the basic steps for using the UPDATE command:

1. Specify the table you wish to update.

2. Specify the columns you wish to modify.

3. Specify the values you wish to use to update the columns.

4. Optionally, specify a WHERE clause to limit which rows are updated.

For example, let's say you have a table called 'customers' with three fields: id, name, and address. To update the address column to a new value for a specific customer, you could use the following command:

UPDATE customers
SET address = '123 Main Street'
WHERE id = 1;

This command will update the address column to '123 Main Street' for the customer with an ID of 1. Without the WHERE clause, the update command would apply to all records in the table.

Deleting data from a database using SQL is a simple process. To delete data from a database, the DELETE statement can be used. It is important to note that the DELETE statement does not remove the table, it only removes the data inside the table.

Below are the steps for deleting data from a database using SQL:

1. Connect to the database using a connection string.

2. Use the DELETE statement with the WHERE clause to specify which records to delete.

3. Execute the statement.

For example, if you wanted to delete all records from a table named “Employees”, you would use the following statement:

DELETE FROM Employees WHERE condition;

In this statement, the WHERE clause is optional. If it is left out, all records in the table will be deleted.

It is important to note that the DELETE statement is irreversible and all data deleted is lost. Therefore, it is best practice to create a backup of the table before deleting any data.