SQL

What are the TRUNCATE statements?

command is used to delete all the rows from the table and free the space containing the table. i.e.: TRUNCATE TABLE Candidates;

What are the DROP statements?

command is used to remove an object from the database. - If you drop a table, all the rows in the table are deleted and the table structure is removed from the database. i.e.: DROP TABLE Candidates;

Explain its different types of INDEX

* Unique and Non-Unique Index: - Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index. i.e.: CREATE UNIQUE INDEX myIndex ON students (enroll_no); - Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently. * Clustered and Non-Clustered Index: - Clustered indexes are indexes whose order of the rows in the database corresponds to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table. - The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index. !!! Clustering indexes can improve the performance of most query operations because they provide a linear-access path to data stored in the database.

What are some common clauses used with SELECT query in SQL?

* WHERE * ORDER BY * GROUP BY * HAVING

What is an Index?

- A database index is a data structure that provides a quick lookup of data in a column or columns of a table. - It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure. i.e.: CREATE INDEX index_name /* Create Index */ ON table_name (column_1, column_2); DROP INDEX index_name; /* Drop Index */

What is a Query?

- A query is a request for data or information from a database table or combination of tables. - A database query can be either a select query or an action query. i.e. /* select query*/ SELECT * FROM myDb.students WHERE student_id=1; /* acquire query*/ UPDATE myDb.students SET fname='Captain',Iname='America' WHERE student_id=1;

What is a View?

- A view in SQL is a virtual table based on the result-set of an SQL statement. - A view contains rows and columns, just like a real table. - The fields in a view are fields from one or more real tables in the database.

What is the difference between Clustered and Non-clustered index?

- Clustered index modifies the way records are stored in a database based on the indexed column. A non-clustered index creates a separate entity within the table which references the original table. - Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower. - In SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.

What is Collation? What are the different types of Collation Sensitivity?

- Collation refers to a set of rules that determine how data is sorted and compared.

What is SQL? What is the difference between SQL and MySQL?

- It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data. - SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

What is MongoDB ?

- MongoDB is an open-source NoSQL database written in C++ language. It uses JSON-like documents with optional schemas. - It provides easy scalability and is a cross-platform, document-oriented database. - MongoDB works on the concept of Collection and Document. - It combines the ability to scale out with features such as secondary indexes, range queries, sorting, aggregations, and geospatial indexes. - MongoDB is developed by MongoDB Inc. and licensed under the Server Side Public License (SSPL).

Difference between a primary key and foreign key

- The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns). - A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.The table with the foreign key constraint is labeled as the child table, and the table containing the candidate key is labeled as the referenced or parent table.

What is a JOIN? List its different types.

- The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two. - There are four different types of JOINs in SQL: INNER, LEFT, RIGHT, FULL

What is DBMS?

= Database Management System. - A system software responsible for the creation, retrieval, updation, and management of the database. It ensures that our data is consistent, organized, and is easily accessible by serving as an interface between the database and its end-users or application software.

What is RDBMS? How is it different from DBMS?

= Relational Database Management System. - The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. - Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.

What is Database?

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

What is a Self-Join?

A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query. i.e.: 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;

What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

What is the difference between an Aggregate function and a Window function?

A window function is like an aggregate function in the sense that it returns aggregate values (eg. SUM(), COUNT(), MAX()). What makes window functions different is that it does not group the result set. The number of rows in the output is the same as the number of rows in the input.

What are Constraints in SQL?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are: + NOT NULL - Restricts NULL value from being inserted into a column. + CHECK - Verifies that all values in a field satisfy a condition. + DEFAULT - Automatically assigns a default value if no value has been specified for the field. + UNIQUE - Ensures unique values to be inserted into the field. + INDEX - Indexes a field providing faster retrieval of records. + PRIMARY KEY - Uniquely identifies each record in a table. + FOREIGN KEY - Ensures referential integrity for a record in another table.

What is a Cross-Join?

Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN. i.e.: SELECT stu.name, sub.subject FROM students AS stu CROSS JOIN subjects AS sub;

What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

Write SQL queries to find a time difference between two events.

First, you can use the LEAD() or LAG() function to create a new column of dates that you want to compare. Then, you can use DATEDIFF to get the difference time between the two events.

Do temporary tables make your code cleaner and faster, one of the two, or none? Why?

Generally, temporary tables are both faster and cleaner. It is much easier to read and follow than subqueries, and in terms of speed, SQL is optimized to do joins rather than subqueries.

Does creating a View require storage in a database?

No, A view does not require any storage in a database because it does not exist physically. The only space that would be required for a view is the space to store the definition of the view, not the data that it presents.

When will ROW_NUMBER and RANK give different results? Give an example.

ROW_NUMBER and RANK will give different results when there are ties within a partition for a particular ordering value.

FULL (OUTER) JOIN

Retrieves all the records where there is a match in either the left or right table. i.e.: SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col;

LEFT (OUTER) JOIN

Retrieves all the records/rows from the left and the matched records/rows from the right table. i.e.: SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col;

RIGHT (OUTER) JOIN

Retrieves all the records/rows from the right and the matched records/rows from the left table. i.e.: SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col;

INNER JOIN

Retrieves records that have matching values in both tables involved in the join. i.e.: SELECT * FROM Table_A JOIN Table_B; SELECT * FROM Table_A INNER JOIN Table_B;

What is the SELECT statement?

SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.

WHERE vs. HAVING

WHERE clause in SQL is used to filter records that are necessary, based on specific conditions. HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records. i.e.: SELECT COUNT(studentId), country FROM myDB.students WHERE country != "INDIA" GROUP BY country HAVING COUNT(studentID) > 5;

Is it possible for LEFT JOIN and FULL (OUTER) JOIN to produce the same results? Why or Why not?

Yes. If every row in the second table can be joined to the first table and every row in the first table can be joined to the second table using a LEFT JOIN, then the result will be the same for a FULL OUTER JOIN.

How would you handle NULLs when querying a data set?

You can handle NULLs when querying using CASE WHEN statements, IFNULL, or COALESCE.

What happens if I GROUP BY a column that is not in the SELECT statement? Why does this happen?

Your query will return no results. This is because the column that you are grouping by needs to be included in the SELECT statement so that the query can identify the values for that given column.

What are the DELETE statements?

statement is used to delete rows from a table. i.e.: DELETE FROM Candidates WHERE CandidateId > 1000;