1.What is normalization? What are the different normalizations?

The process of organizing fields and tables of a database to minimize redundancy and dependency. it allows you to add, delete or modify fields that can be incorporated into a single table. the different normalizations are

• first normal form (1nf): this should remove a table's duplicate columns. it is for the identification of unique columns and the creation of tables for the related data.
• second normal form (2nf): a table is in its second normal form if it meets all requirements of the first normal form and places the subsets of data in separate tables. the relationships between tables are created using primary keys.
• third normal form (3nf): the table should be in the second normal form. there should be no dependency on primary key constraints.
• fourth normal form (4nf): it should meet all the requirements of the third normal form and should not have multi-valued dependencies.

2.What is denormalization?

Using the denormalization technique, you can access the data from higher to lower normal forms of the database. As the name suggests, it is a reversal of normalization, i.e., it introduces redundancy into a table as it incorporates data from the related tables.

3. What is collation? What are the different types of collation sensitivity?

It is a set of rules determining how character data can be sorted and compared. You can use collation to compare A and other language characters. It depends on the width of the characters. You can use ASCII values to compare these character data.
The different types of collation sensitivity are as follows:
• Case sensitivity such as A and a and B and b.
• Accent sensitivity
• Kana sensitivity for Japanese Kana characters.
• Width sensitivity such as for single-byte characters and double-byte characters.

4.What do you understand about CASE statements?

You can use a CASE statement to check certain conditions and return a value based on whether the conditions are evaluated as true. CASE allows you to bring logic, conditions, and order in combination with clauses like WHERE and ORDER BY.
A CASE expression is different from a CASE statement. An expression evaluates a list of conditions. It returns one of the multiple possible result expressions. Its result is a single value, whereas a CASE statement result is the execution of a sequence of statements.

5. What are some common SQL commands?

Some common commands are as follows:

• UNION: It combines the results of two tables. It also removes duplicate rows from the tables.
• MINUS: It returns rows from the first query and not from the second query.
• INTERSECT: It returns rows returned by both the queries.
• DELETE: It removes one or more rows from a table.
• TRUNCATE: It deletes all the rows from the table. Thus, it frees the space containing the table.
• INSERT: It inserts data into the row of a table.
• UPDATE: It updates or modifies the value of a column in the table.
• ALIAS: It is a name that you can give to a table or column. You can refer to this name in a WHERE clause to identify the table or column.
• ALTER: It alters the structure of the database. It allows you to add a column or modify an existing one.
• DROP: It removes tables and databases from RDBMS.
• CREATE: It defines the database structure schema.
• GRANT: It gives the user access privileges to a database.
• REVOKE: It takes back permissions from the user.
• COMMIT: It saves all the transactions to the database.
• ROLLBACK: It allows you to undo transactions that haven't been saved to the database.
• SAVEPOINT: It allows you to set a savepoint within a transaction.
• SELECT: It selects the attribute as described by the WHERE clause.

6. What is SQL and why is it used?

SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It is used to create, update, and query the data stored in these databases. SQL is the standard language for interacting with relational databases and is supported by most relational database management systems (RDBMS) such as MySQL, Oracle, SQL Server, and PostgreSQL.

7. What are the main differences between SQL and other programming languages?

The main differences between SQL and other programming languages are as follows:

• SQL is a declarative language, which means that it is used to describe what data is needed, rather than how to get it. Other programming languages such as C++, Java, or Python are imperative, meaning that they describe how to get the data.
• SQL is specifically designed for working with relational databases, whereas other programming languages are generally more general-purpose.
• SQL is a set-based language, which means that it works with sets of data at a time, rather than individual data elements. Other programming languages are more procedural, meaning that they work with individual data elements.
• SQL is used to manipulate and query data, while other programming languages are used to write programs that perform a wide range of tasks.

8. What are the main data types in SQL?

In SQL, the main data types are:

• INT (integer)
• FLOAT (floating point number)
• CHAR (fixed-length character string)
• VARCHAR (variable-length character string)
• DATE (date)
• DATETIME (date and time)
• BOOLEAN (true or false)

9. How do you select, insert, update and delete data in SQL?

• To select data in SQL, the basic syntax is:
SELECT column1, column2, ... FROM table_name WHERE condition;

• To insert data into a table, the basic syntax is:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• To update data in a table, the basic syntax is:
UPDATE table_name SET column1 = new_value1, column2 = new_value2, ... WHERE condition;

• To delete data from a table, the basic syntax is:
DELETE FROM table_name WHERE condition;

10. What is the difference between a primary key and a foreign key?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. It is used to enforce the integrity of the data in the table and to create relationships with other tables. A foreign key is a column or set of columns in a table that references the primary key of another table. It is used to enforce referential integrity and to create relationships between tables.

11. How do you create a table and its constraints in SQL?

To create a table and its constraints in SQL, you can use the CREATE TABLE statement. Here is an example of how to create a table called "orders" with a primary key and a foreign key:

CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   customer_id INT,
   order_date DATE,
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

13. How do you use aggregate functions in SQL?

Aggregate functions in SQL are used to perform calculations on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

An example of using the SUM aggregate function in SQL would be:

SELECT SUM(price)
FROM products;

14. How do you use subqueries and temporary tables in SQL?

A subquery is a query that is nested inside another query, and it is used to return a set of results that will be used by the outer query. Subqueries can be used in various parts of a SQL statement, such as in the SELECT, FROM, WHERE, and HAVING clauses. Here is an example of how to use a subquery in the WHERE clause of a SELECT statement:

SELECT * FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

A temporary table is a table that exists only for the duration of a session or a transaction and is typically used to store intermediate results for a complex query. To create a temporary table in SQL, you can use the CREATE TEMPORARY TABLE statement, like this:

CREATE TEMPORARY TABLE temp_table AS
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id.

15. How do you optimize and troubleshoot SQL queries?

To optimize and troubleshoot SQL queries, you can use various techniques such as:

• Indexing columns that are frequently searched or sorted on
• Profiling the query execution plan to identify slow or inefficient parts of the query
• Using EXPLAIN or EXPLAIN ANALYZE to understand the query execution plan
• Using tools such as pgAdmin, MySQL Workbench, or SQL Server Management Studio to monitor performance metrics such as CPU and memory usage
• Running the query with different parameters and data to identify any issues with specific values
• Simplifying the query by breaking it down into smaller queries.

16. How do you use indexes and views in SQL?

Indexes in SQL are used to improve the performance of queries by allowing the database management system (DBMS) to quickly locate and retrieve the requested data. An index is a separate data structure that is associated with a table and contains a copy of the data from one or more columns of the table, along with a pointer to the location of the corresponding rows in the table.

For example, to create an index on the "customer_id" column of the "orders" table, you would use the following SQL statement:
CREATE INDEX idx_customer_id ON orders (customer_id);

Views in SQL are virtual tables that are based on the result of a SELECT statement. They do not store data themselves, but rather provide a way to access data from one or more tables in a specific way, such as by filtering, joining, or aggregating the data.

For example, to create a view that shows all orders with a total price greater than $100, you would use the following SQL statement:
CREATE VIEW high_value_orders AS SELECT * FROM orders WHERE total_price > 100;

In SQL, NULL values represent missing or unknown data. When working with NULL values in SQL, it's important to understand the difference between NULL and an empty string or a zero value.

17. How do you handle NULL values in SQL?

There are several ways to handle NULL values in SQL:

1. Using the IS NULL or IS NOT NULL operators in a WHERE clause to filter for or exclude NULL values.
SELECT * FROM customers WHERE last_name IS NOT NULL;

2. Using the COALESCE() or NULLIF() functions to replace NULL values with a specific value or another expression.
SELECT COALESCE(last_name, 'N/A') AS last_name FROM customers;

3. Using the NULL value in a comparison operator to include NULL values in the result set.
SELECT * FROM orders WHERE total_price > 100 OR total_price IS NULL;

It's important to note that when using any comparison operator other than IS NULL or IS NOT NULL, with a NULL value, it will return false, so you need to use the IS NULL or IS NOT NULL operator to handle NULL values in the comparison.

18. How do you implement security in SQL?

Implementing security in SQL involves a combination of several different techniques, including:

1. Access control: This involves limiting access to the database based on user credentials and permissions. Users are assigned different roles and are only granted access to the data they need to perform their job.
2. Encryption: This involves encrypting sensitive data, such as credit card numbers, to protect it from unauthorized access.
3. Auditing: This involves keeping track of all actions performed on the database, such as who accessed it, when, and what data was accessed. This allows for the detection of any unauthorized access attempts.
4. Input validation: This involves validating user input to prevent SQL injection attacks, which are a common type of security vulnerability.
5. Firewall: This involves setting up a firewall to block unauthorized access to the database from the network

19. Write an SQL query to display the current date.

To display the current date in SQL, you can use the GETDATE() function:
SELECT GETDATE();

20.Write an SQL query to verify if the data passed to the query is of the given format: "DD/MM/YY."

To verify if the data passed to a query is in the format "DD/MM/YY," you can use the ISDATE() function along with the appropriate format string:
SELECT ISDATE('DD/MM/YY')

This will return 1 if the passed data is in the format "DD/MM/YY" and 0 otherwise. Please keep in mind that the above query only check the passed string format and not the actual data, to check if the data passed to the query is in the format "DD/MM/YY" and is a valid date you can use the following query

SELECT CASE 
    WHEN ISDATE(your_date_column) = 1 AND your_date_column like '__/__/__' THEN 'valid date' 
    ELSE 'invalid date' 
END as 'date_status'

21.Write an SQL query to print the candidate's name, whose birth date is 08/09/1970 to 30/11/1975.

SELECT name FROM candidates
WHERE birth_date BETWEEN '1970-09-08' AND '1975-11-30';

22.Write a query to print an employee's name whose name starts with 'S.'

SELECT name FROM employees
WHERE name LIKE 'S%';

23. Write a query to find an employee whose salary is less than or equal to 10000.

SELECT * FROM employees WHERE salary <= 10000;

24.Write a query to find the month from a given date.

SELECT MONTH(date_column) FROM table_name;

25.Write a query to join three tables containing two NULL values.

To join three tables containing two NULL values, you can use the LEFT JOIN clause. The LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL values will be returned for the right table's columns:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name
LEFT JOIN table3 ON table2.column_name = table3.column_name

26.Write a query to fetch the first three characters of the employee’s name.

To fetch the first three characters of the employee name from a table called "employee" and a column called "name", you can use the SUBSTRING() function:
SELECT SUBSTRING(name, 1, 3) as 'name_first_3' FROM employee;

This query will return a new column called "name_first_3" containing the first three characters of the "name" column for each employee.

27.Consider the following data table for answering the question given below:

EmpID	EmpFname	EmpLname	Department	Project	Address	DOB	Gender
1	Jacob	Roberts	HR	P1	Las Vegas (LAS)	01/12/1976	M
2	Rebecca	James	Admin	P2	Texas (TEX)	02/05/1968	F
3	Ron	Peter	Account	P3	Dallas (DAL)	01/01/1980	M
4	Lucy	Winget	HR	P1	San Diego (SAN)	02/05/1992	F
5	Marina	Walters	Admin	P2	Phoenix (PHO)	03/07/1994	F

28.Write a SQL query to retrieve the place name, i.e., the string before brackets, from the Address column.

SELECT SUBSTRING_INDEX(Address,'(',1) as Place FROM Employee;

29.Write a SQL query to fetch the names of employees that begin with 'R.'

SELECT EmpFname FROM Employee WHERE EmpFname LIKE 'R%';

30. How would you debug your code in PL/SQL?

To debug your code in PL/SQL, you can use the following methods:

• DBMS_OUTPUT.PUT_LINE : You can use this package to print your variable values, messages, etc.
• RAISE_APPLICATION_ERROR: This function allows you to raise an error with a custom error message.
• Using Oracle SQL Developer : It is an Integrated development environment (IDE) for working with SQL and PL/SQL. It provides debugging facilities like breakpoints, step-by-step execution and watch variables.

It's important to note that debugging PL/SQL can be more involved than debugging other programming languages, and it may require knowledge of the specific database and tools you are using.

31.What virtual tables are available during the execution of the database trigger?

• Virtual tables available during the execution of a database trigger include:
• INSERTED: Contains the new data for any rows affected by an INSERT or UPDATE statement.
• DELETED: Contains the old data for any rows affected by an UPDATE or DELETE statement.

32.Write a query to fetch the first three characters of the employee name.

To fetch the first three characters of the employee name from a table called "employee" and a column called "name", you can use the SUBSTRING() function:
SELECT SUBSTRING(name, 1, 3) as 'name_first_3' FROM employee;

This query will return a new column called "name_first_3" containing the first three characters of the "name" column for each employee.