Explore Cognizant SQL Interview Questions and Answers
Preparing for a Cognizant SQL interview, check out this SQL interview questions library. SQL is a type of standard database language most commonly found in databases that access and manipulate data directly using a table-like structure. SQL is one of the most important tech skills that everyone needs nowadays because everyone is talking about data, whether it is for a complete Freshers or an experienced professional.
In this article, we are going to go through 20+ Cognizant SQL Interview Questions with answers asked in SQL developer interview who has recently faced the interview. We have collected this information from multiple trusted ref. This SQL interview guide will help us to shine in highly competitive job interviews regardless of whether we need to prepare for our first data related job or to climb up further.
What is SQL?
SQL (Structured Query Language) is a standard query language for interacting with relational databases. It enables users to create, read, updated and delete data, and it offers commands to define the schema of the database and to control database security.
What is a database?
A database is an electronic system that allows for the collection and organization of data, usually in tabular form (with rows and columns). Managed by a database management system (DBMS), it can store data efficiently, retrieve data and manipulate data.
What exactly are the different types of SQL commands?
SQL commands are broadly classified as:
- DDL (Data Definition Language) : CREATE, ALTER, DROP, TRUNCATE
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
- DCL(Data Control Language): GRANT,REVOKE.
- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT, etc
How to distinguish CHAR and VARCHAR2 data types?
CHAR: Fixed-length storage. If only part of the set minimum length is used, it pads the result with spaces.
VARCHAR2: storage variable length. This is space-efficient as it only stores the actual data when the full length isn’t required.
What is a primary key?
The first column is the primary key which uniquely identifies every record in the table. Referential Integrity or Unique Integrity with primary key column(s) It does not allow to insert duplicate values in to primary key column(s) or will not allow to insert NULL value.
What is a foreign key?
One of the uses of a foreign key is a column (or set of columns) in one table, that references a row in another table or the primary key. It creates and enforces the relationship between first and second table and maintains the data integrity.
DEFAULT constraint purpose
The DEFAULT constraint provides a default value for a column when none is specified during an INSERT. The consistent data and relative ease of data entry is one upside.
Now, Let’s us understand what you mean by normalization in databases.
Normalization — It is the process of placing data based in a database to eliminate redundancy and improve data integrity Normalization – This is the process of breaking down the large tables into smaller related tables and defining the relation between them to maintain consistency and avoid anomaly.
Q: What is denormalization, and when do we use it?
Denormalization is the technique of joining the normalized tables into a bigger table for the sake of performance. When queries get complex and joins take time then it is time to consider denormalization when and performance gain is more than the redundancy cost.
What is a query in SQL?
A query is an SQL statement to extract, update or modify the data from the database. The simplest query is SELECT which is used to retrieve data from one or more tables according to certain conditions.
SQL Operators — Introduction : SQL has many different operators.
- Below are Arithmetic Operators +, -, *, /, %
- Comparison Operators: =,! =, , >, =, <=
- Logical rejects: AND, OR, NOT
- Union Difference Of Setsimulator
- LINE Operators: BETWEEN,IN,LIKE,IS NULL
What is a view in SQL?
A view is a SELECT query, and hence a virtual table. It does not store data by itself, as it simply displays data contained in one or more tables in a more user-friendly manner. Using views makes it easier to read complex queries, and restrictions on views present an additional layer of security by restricting the access of a user to selected rows or columns.
What is the use of UNIQUE constraint?
The UNIQUE constraint ensures that the values in a column, or a combination of columns are unique. This ensures that no duplicates are present and saves the integrity of data.
Type of joins in SQL
- INNER JOIN: meaning both tables have matched rows
- LEFT JOIN (LEFT OUTER JOIN): Outputs all records from the left table and matched records from the right table.
- This means RIGHT JOIN (RIGHT OUTER JOIN) gets all the rows from the right table, and the matching rows from the left table.
- FULL OUTER JOIN (FULL JOIN): Return all records if there is a match in either left or right table records
- CROSS JOIN: returns the Cartesian product of two tables.
What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN: it returns only the rows in which there are matches in both tables
- OUTER JOIN: Returns all records from one of the tables (LEFT, RIGHT, or FULL), along with the matched records from the other table. For the non-matching side, NULL values are returned if there is no match.
A GROUP BY clause is used for what purposes?
GROUP BY The GROUP BY clause is used to group the same data in a group. Usually, it is used together with aggregate functions (COUNT, SUM, AVG, etc.) to do calculations on each group instead of the whole dataset.
SQL Aggregate Functions Explained
Aggregate functions are special functions that perform a calculation on a set of values and return a single value. Some of the common aggregate functions are:
- COUNT(): It gives the number of rows.
- SUM(): Return the sum of values.
- AVG(): Calculates average of values.
- MIN(): That one which has the minimum value.
- MAX() : It returns the maximum value.
What is a subquery?
Subquery: A query within another query. Generally, the subquery in which we have to use is in WHERE Clause, it is easier to use grouping to filter data sets based on the results of another query.
When to use WHERE and HAVING respectively?
NOTE: WHERE filters rows prior to grouping.
HAVING: It filters the grouped data after the GROUP BY clause is executed.
In short, WHERE is for single rows but HAVING is for groups.
What is an index, and why do we need an index?
Indexes are database objects that allow the retrieval of rows sought by querying quickly (using the least possible time). They work like an index in a book and help us locate specific data faster without reading the whole table. That being said, indexes need extra space to store and atlittle to no cost will make data manipulation operations alittle slower.
As stated above, DELETE command actually removes the records from a table, whereas TRUNCATE command clears the content in a table.
DELETE: Deletes rows individually and logs the deletion to the transaction log, making it rollable. It can have a WHERE clause.
TRUNCATE − While it deletes all the rows in a table, it does not keep the record of each row that it removes. Cannot have a WHERE clause and is faster on large data sets compared to DELETE.
Note: For information on SQL ORDER BY, see SQL ORDER BY: What Is It?
The ORDER BY clause orders the query result set by one or more columns either in ascending (default) or descending order. This assists in ordering the data in a way that makes more sense or is easier to read.
How do SQL and NoSQL databases differ?
SQL Databases:
- The PowerBI table visual is a structured table with rows and columns.
- Rely on a fixed schema.
- Offer ACID properties.
NoSQL Databases:
- But rather use flexible schema-less inside structures, for example key-value pairs or document stores.
- Are horizontally scalable by design.
- Loosely consistent — Usually focused on performance / scalability than strict consistency
What is a table in SQL?
A table is an unordered set of data that possesses a correlation assembled in cells in a row and a column. It can be understood as a set through the modelling of column and row; columns define the type of data held and rows hold each database entry.
Common constraints include:
- NOT NULL: Ensures that a column cannot contain NULL values
- UNIQUE: Restricts column values to a unique set.
- PRIMARY KEY: Creates a unique identity for each row in a table.
- FOREIGN KEY: Ensures referential integrity by referencing a primary key from another table.
- CHECK: Validates that the values of a column meet a certain condition.
- DEFAULT: Assigns a default value to a column when no value is specified
What is a cursor in SQL?
Cursor is a database object which is used to receive, manipulate and navigate through rows fetched from a resultset one row at a time. Cursors are useful for a scenario where you cannot handle the operations set-based and it needs to be processed row by row.
What is a trigger in SQL?
Triggers are a collection of SQL statements that are automatically invoked/ fired when a specific event occurs on a particular table like INSERT or UPDATE or DELETE statement. Using triggers we can keep data consistent, enforce business rules, and implement a complex integrity constraints.
What is the function of SQL SELECT Statement?
Data from the tables can be fetched using SELECT statement. SQL has many commands, but the select command is by far the most widely used.
What are NULL values in SQL?
NULL — missing / unknown value It’s not zero or empty string. NULL values indicate loss of data or inapplicability of data.
What is a stored procedure?
A stored procedure is a ready to execute bundle of SQL statements stored in the database. It can accept parameters, encapsulate logic and queries, and return one or more output values or result sets. SP centralizes business logic, which results in performance and maintainability advantages.
