SQL

What do you understand by a character manipulation function?

Character manipulation functions are used for the manipulation of character data types. Some of the character manipulation functions are: - UPPER: It returns the string in uppercase. - LOWER: It returns the string in lowercase. - INITCAP: It converts the first letter of the string to uppercase and retains others in lowercase. - CONCAT: It is used to concatenate two strings. - LENGTH: It is used to get the length of a string.

What is the difference between Primary key and Unique Key?

Both Primary and Unique key carry unique values but the primary key can not have a null value where the Unique key can. And in a table, there cannot be more than one Primary key but unique keys can be multiple.

What is a unique constraint?

Unique constraints ensure that all the values in a column are different Syntax - create table stu2(s_id int unique, s_name varchar(20))

What are Views? Give an example.

Views are virtual tables used to limit the tables that we want to display, and these are nothing but the result of a SQL statement that has a name associated with it. Since views are not physically present, they take less space to store. Syntax: create view female_employee as select * from employee where e_gender='Female' select * from female_employee

State the differences between views and tables.

Views: - ViewsTablesIt is a virtual table that is extracted from a database. - Views do not hold data themselves. - A view is also utilized to query certain information contained in a few distinct tables. - In a view, we will get frequently queried information. Table: - A table is structured with a set number of columns and a boundless number of rows. - A table contains data and stores the data in databases. - A table holds fundamental client information and the cases of a characterized object. - In a table, changing the information in the database changes the information that appears in the view

State the differences between clustered and non-clustered indexes.

- Clustered index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. We can open the book at 'David' (for 'David, Thompson') and find information for all Davids right next to each other. Since the data is located next to each other, it helps a lot in fetching data based on range-based queries. Also, the clustered index is actually related to how the data is stored. There is only one clustered index possible per table. Used for - Sorting and storing records physically in memory Methods for storing - Stores data in the leaf nodes of the index Size - Quite large Data accessing - Fast Additional disk space - Not required Type of key - By default, the primary key of a table is a clustered index Main feature - Improves the performance of data retrieval - Non-clustered index: It stores data at one location and indexes at some other location. The index has pointers that point to the location of the data. As the index in the non-clustered index is stored in different places, there can be many non-clustered indexes for a table. Used for - Creating a logical order for data rows. Pointers are used for physical data files Methods for storing - Never stores data in the leaf nodes of the index Size - Comparatively, small Data accessing - Slow Additional disk space - Required to store indexes separately Type of key - It can be used with the unique constraint on the table that acts as a composite key Main feature - Should be created on columns used in Joins

What are the subsets of SQL?

- DDL(Data Definition Language) - DML(Data Manipulation Language) - DCL(Data Control Language) - TCL(Transaction Control Language)

Explain the different types of SQL commands.

- Data Definition Language: DDL is that part of SQL which defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are: Create table, Alter table, Drop table - Data Manipulation Language: DML is used to manipulate the already existing data in the database. That is, it helps users retrieve and manipulate the data. It is used to perform operations such as inserting data into the database through the insert command, updating the data with the update command, and deleting the data from the database through the delete command. - Data Control Language: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and also to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke. - Transaction Control Language: It is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction into logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

What are Entities and Relationships?

- Entities: Entity can be a person, place, thing, or any identifiable object for which data can be stored in a database. For example: In a company's database, employees, projects, salaries, etc can be referred to as entities. - Relationships: Relationships between entities can be referred to as the connection between two tables or entities. For example: In a college database, the student entity and department entities are associated with each other.

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

- FROM The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given. - WHERE The WHERE clause defines the parameters that would be used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects. - GROUP BY This GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions. - ORDER BY ORDER BY clause helps you to choose the columns on which the table's result should be sorted. - HAVING By using an aggregate function, the HAVING clause filters the results of the GROUP BY clause.

What are the different types of database management systems?

- Hierarchical database: It is a tree-like structure where the data is stored in a hierarchical format. In this database, the parent may have many children but a child should have a single parent. - Network database: It is presented as a graph that allows many-to-many relationships. This database allows children to have multiple children. - Relational database: A relational database is represented as a table. The values in the columns and rows are related to each other. It is the most widely used database because it is easy to use. - Object-Oriented database: The data values and operations are stored as objects in this database. All these objects have multiple relationships between them.

What do you understand by normalization and denormalization?

- Normalization is used in reducing data redundancy and dependency by organizing fields and tables in databases. It involves constructing tables and setting up relationships between those tables according to certain rules. The redundancy and inconsistent dependency can be removed using these rules to make it more flexible. - Denormalization is contrary to normalization. In this, we basically add redundant data to speed up complex queries involving multiple tables to join. Here, we attempt to optimize the read performance of a database by adding redundant data or by grouping the data.

Explain the difference between OLTP and OLAP.

- OLTP: It stands for Online Transaction Processing, and we can consider it to be a category of software applications that is efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to keep up the consistency. The OLTP system often follows decentralized planning to keep away from single points of failure. This system is generally designed for a large audience of end-users to perform short transactions. Also, queries involved in such databases are generally simple, need fast response time, and in comparison, return only a few records. So, the number of transactions per second acts as an effective measure for those systems. - OLAP: OLAP stands for Online Analytical Processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions. For OLAP systems, the efficiency of computing depends highly on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multi-dimensional schemas.

Explain the types of indexes.

- Single-column Indexes: A single-column index is created for only one column of a table. SYNTAX: CREATE INDEX index_name ON table_name(column_name); - Composite-column Indexes: A composite-column index is an index created for two or more columns of the table. SYNTAX: CREATE INDEX index_name ON table_name (column1, column2) - Unique Indexes: Unique indexes are used for maintaining the data integrity of the table. They do not allow multiple values to be inserted into the table. SYNTAX: CREATE UNIQUE INDEX index ON table_name(column_name)

What are the types of views in SQL?

1. Simple View: A view that is based on a single table and does not have a GROUP BY clause or other features. 2. Complex View: A complex view is one that is built from several tables and includes a GROUP BY clause as well as functions. 3. Inline View: A view that is built on a subquery in the FROM Clause, which provides a temporary table and simplifies a complicated query. 4. Materialized View: A view that saves both the definition and the details. It builds data replicas by physically preserving them.

What is Cursor? How to use a Cursor?

A database Cursor is a control that allows you to navigate around the table's rows or documents. It can be referred to as a pointer for a row in the set of rows. Cursors are extremely useful for database traversal operations like extraction, insertion, and elimination. - After any variable declaration, DECLARE a cursor. A SELECT Statement must always be aligned with the cursor declaration. - To initialize the result set, OPEN statements must be called before fetching the rows from the result table. - To grab and switch to the next row in the result set, use the FETCH statement. - To deactivate the cursor, use the CLOSE expression. - Finally, use the DEALLOCATE clause to uninstall the cursor description and clear all the resources associated with it.

What is a foreign key?

A foreign key is an attribute or a set of attributes that references to the primary key of some other table. Basically, it is used to link together two tables. Syntax - CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) )

What is a primary key?

A primary key is used to uniquely identify all table records. It cannot have NULL values, and it must contain unique values. A table can have only one primary key that consists of single or multiple fields. Syntax - CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, PRIMARY KEY (ID) );

What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system. We can consolidate several SQL statements into a stored procedure and execute them whenever and wherever required. A stored procedure can be used as a means of modular programming, i.e., we can create a stored procedure once, store it, and call it multiple times as required. This also supports faster execution when compared to executing multiple queries. Syntax: CREATE PROCEDURE procedure_name AS Sql_statement GO; To execute we will use this: EXEC procedure_name

What do you understand by a temporary table? Write a query to create a temporary table.

A temporary table helps us store and process intermediate results. These temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where we need to store temporary data. Syntax: 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

What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table. Since the primary key is unique for each record, we add this primary field as the AUTO_INCREMENT field so that it is incremented when a new record is inserted. The AUTO-INCREMENT value is by default starts from 1 and incremented by 1 whenever a new record is inserted.

What do you mean by table and field in SQL?

An organized data in the form of rows and columns is said to be a table. Here rows and columns are referred to as tuples and attributes. And the number of columns in a table is referred to as a field. In the record, fields represent the characteristics and attributes.

What is a default constraint?

Constraints are used to specify some sort of rules for processing data and limiting the type of data that can go into a table. Now, let's understand the default constraint. The default constraint is used to define a default value for a column so that the default value will be added to all the new records if no other value is specified. For example, if we assign a default constraint for the E_salary column in the below table and set the default value as 85000, then all the entries of this column will have a default value of 85000 unless no other value has been assigned during the insertion. Syntax - create table stu1(s_id int, s_name varchar(20), s_marks int default 50)

What are the usages of SQL?

Creating new databases Inserting new data Deleting existing data Updating records Retrieving the data Creating and dropping tables Creating functions and views Converting data types

What is the difference between DELETE and TRUNCATE commands?

DELETE: This query is used to delete or remove one or more existing tables. TRUNCATE: This statement deletes all the data from inside a table. - TRUNCATE is a DDL command, and DELETE is a DML command. - With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger. - If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command.

What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG() and VARIANCE() are some of the most widely used group functions.

What is an index?

Indexes help speed up searching in the database. If there is no index on any column in the WHERE clause, then SQL Server has to skim through the entire table and check each and every row to find matches, which might result in slow operation on large data. Indexes are used to find all rows matching with some columns and then to skim through only those subsets of the data to find the matches. Syntax - CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

Explain Inner Join.

Inner Join basically gives us those records that have matching values in two tables. Let us suppose, we have two tables Table A and Table B. When we apply Inner Join on these two tables, we will get only those records that are common to both Table A and Table B. Syntax: SELECT columns FROM table1 INNER JOIN table2 ON table1.column_x=table2.column_y;

What is SQL?

SQL stands for 'Structured Query Language' and is used for communicating with databases. According to ANSI, SQL is the standard query language used for maintaining relational database management systems (RDBMS) and also for performing different operations of data manipulation on different types of data. Basically, it is a database language that is used for the creation and deletion of databases, and it can be used to fetch and modify the rows of a table and also for multiple other things.

What is the difference between SQL and MySQL?

SQL: - It is a structured query language used in a database - It is used for query and operating database system - SQL is always the same - Only a single storage engine is supported in SQL - The server is independent in SQL MySQL: - It is a database management system - It allows data handling, storing, and modifying data in an organized manner - MySQL keeps updating - MySQL supports multiple storage engines - During backup sessions, the server blocks the database.

State the differences between SQL and PL/SQL.

SQL: - SQL is a database structured query language. - SQL is an individual query that is used to execute DML and DDL commands. - SQL is a declarative and data-oriented language. - It is mainly used for the manipulation of data. - It provides interaction with the database server. - It cannot contain PL/SQL code in it. PL/SQL: - It is a programming language for a database that uses SQL. - PL/SQL is a block of codes used to write the entire procedure or a function. - PL/SQL is a procedural and application-oriented language. - It is used for creating an application. - It does not provide interaction with the database server. - It can contain SQL in it because it is an extension of SQL.

What do you understand by Self Join?

Self Join in SQL is used for joining a table with itself. Here, depending upon some conditions, each row of the table is joined with itself and with other rows of the table. Syntax: SELECT a.column_name, b.column_name FROM table a, table b WHERE condition

Describe how to delete duplicate rows using a single statement but without any table creation.

Syntax: DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id

How can you copy data from one table into another?

Syntax: insert into employee_duplicate select * from employees

What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null value.

What is the use of the Intersect operator?

The Intersect operator helps combine two select statements and returns only those records that are common to both the select statements. So, after we get Table A and Table B over here and if we apply the Intersect operator on these two tables, then we will get only those records that are common to the result of the select statements of these two. Syntax: SELECT column_list FROM table1 INTERSECT SELECT column_list FROM table2

What do you know about Joins? Define different types of Joins.

The Join clause is used to combine rows from two or more tables based on a related column between them. There are various types of Joins that can be used to retrieve data, and it depends upon the relationship between tables. 1. Inner Join: Inner Join basically returns records that have matching values in both tables. 2. Left Join: Left Join returns rows that are common between the tables and all the rows of the left-hand-side table, i.e., it returns all the rows from the left-hand-side table even if there are no matches available in the right-hand-side table. 3. Right Join: Right Join returns rows that are common between the tables and all the rows of the right-hand-side table, i.e., it returns all the rows from the right-hand-side table even if there are no matches available in the left-hand-side table. 4. Full Join: Full Join returns all the rows from the left-hand-side table and all the rows from the right-hand-side table.

What is the difference between Union and Union All operators?

The Union operator is used to combine the result set of two or more select statements. For example, the first select statement returns the fish shown in Image A, and the second returns the fish shown in Image B. Then, the Union operator will return the result of the two select statements as shown in Image A U B. Also, if there is a record present in both tables, then we will get only one of them in the final result.

What is the ACID property in a database?

The full form of ACID is Atomicity, Consistency, Isolation, and Durability. To check the reliability of the transactions, ACID properties are used. - Atomicity refers to completed or failed transactions, where transaction refers to a single logical operation on data. This implies that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged. - Consistency means that the data meets all of the validity guidelines. The transaction never leaves the database without finishing its state. - Concurrency management is the primary objective of isolation. - Durability ensures that once a transaction is committed, it will occur regardless of what happens in between, such as a power outage, a fire, or some other kind of disturbance.

What is a Unique Key?

The key which can accept only the null value and cannot accept the duplicate values is called Unique Key. The role of the unique key is to make sure that each column and row are unique. Syntax - CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, UNIQUE(ID) );

What do you know about the stuff() function?

The stuff function deletes a part of the string and then inserts another part into the string starting at a specified position. Syntax: STUFF(String1, Position, Length, String2) Here, String1 is the one that would be overwritten. Position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that would overwrite String1.

What is a "TRIGGER" in SQL?

The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands like insert, update, and delete are given.

Explain database white box testing and black box testing.

The white box test method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following: - As the coding error can be detected by testing the white box, it can eliminate internal errors. - To check for the consistency of the database, it selects the default table values. - This method verifies the referential integrity rule. - It helps perform the module testing of database functions, triggers, views, and SQL queries. The black box test method generally involves interface testing, followed by database integration. It includes: - Mapping details - Verification of the incoming data - Verification of the outgoing data from the other query functions

What is the difference between BETWEEN and IN operators in SQL?

To represent rows based on a set of values, use the BETWEEN operator. The values may be numbers, text, or dates. The BETWEEN operator returns the total number of values that exist between two specified ranges. To search for values within a given range of values, the IN condition operator is used. If we have more than one value to choose from, we use the IN operator.

State the differences between HAVING and WHERE clauses.

WHERE: - Implemented in row operations - Applied to a single row - Used for fetching specific data from specific rows according to the given condition - Can't have aggregate functions - Can be used with SELECT, UPDATE, and DELETE - Comes before GROUP BY HAVING: - Implemented in column operations - Applied to the summarized row or groups - Used for fetching the entire data and separating according to the given condition - Can have aggregate functions - Cannot be used without a SELECT statement - Comes after GROUP BY