SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It provides a standardized way to interact with databases, enabling users to perform various operations such as querying data, inserting, updating, and deleting records, creating and modifying database structures, and managing access control.
These questions cover some fundamental concepts of SQL, including data manipulation, querying, database design, and optimization. It’s advisable to have a good understanding of these concepts and be able to provide clear and concise explanations. Additionally, practicing SQL queries and familiarizing yourself with common functions and syntax can greatly enhance your preparation for SQL interviews.
SQL stands for Structured Query language. It is a database language that allows you to manage, manipulate and retrieve data from relational databases. It is also the standard database language used by all Relational Database Management Systems (RDBMS) such as SQL Server, MySQL, PostgreSQL, Oracle Database, IBM DB2, Sybase, and Microsoft Access. A query creates a virtual table based on existing tables or constants built into tables. Syntax: { ( Query [ ORDER BY clause ] [ result offset clause ] [ fetch first clause ] ) | Query INTERSECT [ ALL | DISTINCT ] Query | Query EXCEPT [ ALL | DISTINCT ] Query | Query UNION [ ALL | DISTINCT ] Query | SelectExpression | VALUES Expression } SQL Query is a combination of keywords, identifiers and clauses. It includes three main clauses that are SELECT, FROM, and WHERE clause. Basic structure of SQL Query: SELECT <field>, <field>, <field> FROM <database table> WHERE <field> = ‘Value’; In Simple Syntax: SELECT column1, column2 FROM table1, table2 WHERE column2=’value’ Here, SELETE – This clause used to specify one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. — column1, column2… are the fields of a table whose values you want to fetch. FROM – This clause used to specify the table(s) that contain the data. ……from table1, table2 WHERE – This clause used to specify the database table(s) from which you want your query to display data. ……. column2=’value’; ORDERBY – This clause used to specify the order in which the records appear in a query. In the SELECT clause, include the fields that you want to use in the SQL query’s ORDER BY (and GROUP BY) clauses. In SQL, WHERE clause is used to further restrict the number of records that are retrieved from the database to only those that are of interest. The clause typically takes the form of one or more ‘conditions. Only those records that meet one or more of the conditions are retrieved from the specified database table. The HAVING clause is used in database systems to fetch the data/values from the groups according to the given condition. The syntax for the HAVING clause in SQL is: SELECT column1, column2, … column1n_n, aggregate_function (aggregate_ column) FROM table Name GROUP BY column1, column2, … column_n HAVING condition; The only difference is that the WHERE clause cannot be used with aggregate functions, whereas the HAVING clause can use aggregate functions. IN clause retrieves all records which match with the given set of values. It acts as multiple OR conditions. EXISTS clause is a Boolean operator that returns either True or False. It’s used in combination to a sub-query. The EXISTS clause is faster than IN clause when the subquery results are very large. The IN clause is faster than EXISTS clause when the subquery results are very small. SUM: This function adds all the values in a data set and returns that value. Syntax: SUM(ALL | DISTINCT column) MAX: This returns the largest value in a set of numbers. Syntax: MAX(column | expression) MIN: This returns the smallest value in a set of numbers. Syntax: MIN(column | expression) COUNT: The COUNT function reports the number of rows in a group and includes ones with null values. Syntax: COUNT ( [ALL | DISTINCT] column | expression | *) AVG: This function calculates the average of numerical values in a collection. Syntax: AVG( ALL | DISTINCT) SQL Commands are divided into five different categories. There are – DDL, DML, DCL, DQL, and TCL. It is used to define the structure of a database. It means creating new tables and objects or altering their attributes (such as their data type, table name, etc.) Syntax: CREATE TABLE table_name; Syntax: DROP TABLE table_name; Syntax: TRUNCATE TABLE table_name Syntax: ALTER TABLE table_name ADD column_name datatype; Syntax: BACKUP DATABASE: It is used to manipulate and perform operations on data in a database. Syntax: UPDATE table_name SET my_column = my_value WHERE my_column = my_value; Syntax: DELETE FROM table_name WHERE my_column = my_value; Syntax: SELECT column_name FROM table_name; Syntax: USE DatabaseName; Syntax: INSERT INTO table_name (column_1, column_2, column_3) Syntax: MERGE target_table USING source_table ON merge_condition WHEN MATCHED THEN update_statement WHEN NOT MATCHED THEN insert_statement WHEN NOT MATCHED BY SOURCE THEN DELETE statment; It is used to control user permissions and access to a database. Syntax: GRANT privileges ON object TO user; REVOKE: This command is used to revokes the given access to the user. Syntax: REVOKE privileges ON object FROM user; It is used to fetch the data from the database. Syntax: A particular column from the table can be selected with, SELECT column_name FROM Table_Name; And all the columns can be selected by using * as shown below, SELECT * FROM Table_Name; TCL-Transaction Control Language: It is a set of special commands that managing and controlling transactions within the database. Syntax: COMMIT; Syntax: ROLLBACK; ( or ) ROLLBACK [WORK] [TO SAVEPOINT] Syntax: SAVEPOINT savepoint_name; Syntax: SET TRANSACTION [Read Write | Read Only]; SELECT – This command is used to extracts data from a database UPDATE – This command is used to updates data in a database DELETE – This command is used to deletes data from a database INSERT INTO – This command is used to inserts new data into a database CREATE DATABASE – This command is used to creates a new database ALTER DATABASE – This command is used to modifies a database CREATE TABLE – This command is used to creates a new table ALTER TABLE – This command is used to modifies a table DROP TABLE – This command is used to deletes a table CREATE INDEX – This command is used to creates an index (search key) DROP INDEX – This command is used to deletes an index Constraints in SQL can be defined at the column level, where it is specified as part of the column definition and will be applied to that column only, or declared independently at the table level. In other words, if the inserted data meets the constraint rule, it’ll be inserted successfully on server. However, the insert operation will be revoked, If the inserted data violates the defined constraint. SQL allows two types of constraints. It could be column level or table level. Column level: This Constraint applied only to one column. Table Level: This Constraint applied to the whole table. Following are most commonly used constraints in SQL: NOT NULL Constraint: this constraint is used to ensure that a column cannot have NULL value. CREATE INDEX: this constraint is used to create and retrieve data from the database very quickly. UNIQUE Constraint: this constraint is used to ensure that all values in a column are different. CHECK Constraint: this constraint is used to ensure that all values in a column satisfy certain conditions. PRIMARY Key: this constraint is used to Uniquely identified each rows/record in a database table. t is a combination of NOT NULL and UNIQUE constraints. FOREIGN Key: this constraint is used to Uniquely identified a rows/record in any another database table. DEFAULT Constraint: this constraint is used to provide a default value for a column when none is specified. NUMBER: A variable-length column. Allowed values are zero, positive and negative numbers Syntax: NUMBER (n, d) CHAR: A variable length field up to 255 characters in length Syntax: CHAR (size) VARCHAR/VARCHAR2: A variable length field up to 2000 character in length Syntax: VARCHAR (size) / VARCHAR2(size DATE/TIME: A fixed length field. The time is stored as a part of the date. The default format is DD/MON/YY Syntax: data or time LONG: A variable length filled up to 2 GB in length Syntax: long in Oracle PL/SQL, RAW is a data type used to store binary data or data which is in byte-oriented form is called Raw data type. RAW data is that it can only be queried or inserted; RAW data cannot be manipulated. RAW data is always returned as a hexadecimal character value. ALL: This operator is used to compare a value to all values in another value set. AND: It allows the existence of multiple conditions in an SQL statement WHERE clause. ANY: This operator is used to compare a value to any applicable value in the list according to the condition. BETWEEN: This operator is used to search for values that are within a set of values, given the minimum value and the maximum value. EXISTS: This operator is used to search for the presence of a row in a specified table that meet certain criteria. IN: this operator is used to compare a value to a list of literal values that have been specified. LIKE: this operator is used to compare a value to similar values using wildcard operators. NOT: this operator is used to reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. OR: this operator is used to combine multiple conditions in an SQL statement’s WHERE clause. IS NULL: this operator is used to compare a value with a NULL value. UNIQUE: this operator is used to searches every row of a specified table for uniqueness (no duplicates). There are different types of joins available in SQL − INNER JOIN: It is used to returns rows when there is a match in both tables. LEFT JOIN: It is used to returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN: It is used to returns all rows from the right table, even if there are no matches in the left table. FULL JOIN: It is used to returns rows when there is a match in one of the tables SELF JOIN: It is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. CARTESIAN JOIN: It is used to returns the Cartesian product of the sets of records from the two or more joined tables. It is used to join large tables or in an instance where the user wants most of the joined table rows. It is structured in such a way that, columns with the same name of associated tables will appear once only. (Don’t use ON clause in a natural join). Syntax: TableExpression NATURAL [ {LEFT | RIGHT} [ OUTER ] | INNER ] JOIN { TableViewOrFunctionExpression | ( TableExpression ) } By using the DISTINCT keyword. Syntax: Select DISTINCT CompanyID from compnay To create empty tables using the INTO operator EXITS Clause triggers are database objects, actually, a special kind of stored procedure, which “reacts” to certain actions we make in the database. STUFF function is used to overwrite existing character or inserts a string into another string. Syntax: STUFF(string_expression,start, length, replacement characters) SQL Server is a database management system handled by Microsoft. It’s mostly used in Microsoft server environment that uses SQL. while SQL is a query language and not a database system. By default, SQL Server runs on port 1433. SQL is a query language for the database. It is used to handle and maintain data i.e.to perform the CRUD operations. It is used to manage the RDBMS databases. It supports a single storage engine. MySQL is a relational database management system (RDBMS) that uses SQL to query the database. It is used to work on the database through its GUI tool. It supports multiple storage engines. DELETE is a DML Command. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. Only the rows and columns specified in the WHERE clause condition will be deleted. TRUNCATE is a DDL command. The truncate command removes the complete data from an existing table but not the table itself. In TRUNCATE, WHERE clause cannot be used. ON A.key = B.key INNER JOIN TableB B FROM TableA A SELECT <fields> FROM TableA A INNER JOIN TableB B ON A.key = B.key SELECT <fields> FROM TableA A LEFT JOIN TableB B ON A.key = B.key SELECT <fields> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.keyWhat is SQL?
What is a Query in SQL?
How to write a SQL Query?
What is use of ORDER BY clause in SQL?
What is the use of WHERE Clause in SQL?
What is use of HAVING clause in SQL?
What is the main difference between WHERE clause and HAVING clause?
What is the difference between IN clause and EXISTS clause?
What are the important Aggregate Functions in SQL?
What are the different types of SQL Commands?
DDL-Data Definition Language:
DML-Data Manipulation Language:
DCL-Data Control Language:
DQL-Data Query Language:
What are most used SQL commands?
What are the SQL Constraints?
What are the most commonly used constraints in SQL?
What are the most common data types of SQL?
What is the use of Raw Data type?
What are the Logical Operators in SQL?
The following are the most common logical operators of SQL:
What are the joins in SQL?
What is a use of Hash Join?
What is use of Nature Join?
How can you select unique records from a table?
How to create empty tables with the same structure as another table?
Which is the faster clause to use in SQL?
What are Triggers in SQL?
What is use of STUFF function?
What is the difference between SQL and SQL server?
Which TCP/IP port does SQL Server run?
What is the difference between SQL and MySQL?
What is the difference between DELETE and TRUNCATE commands in SQL?
Related posts:
- Cassandra Interview Questions and Answers
- Database Interview Questions and Answers
- JDBC Interview Questions and Answers
- Neo4j Interview Questions and Answers
- SQL Interview Questions and Answers
- SQL Server 2008 Interview Questions and Answers
- SQL Server DBA Interview Questions and Answers
- SQLite Interview Questions and Answers