Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL Interview Questions > Interview Questions > Flashcards

Interview Questions Flashcards

This set of questions came from edureka! on YouTube

1
Q

DELETE and TRUNCATE

A

DELETE

  • used to delete a row in a table
  • you can rollback data after delete
  • DML comand
  • slower

TRUNCATE

  • delete all the rows in a table
  • cannot rollback data
  • DDL command
  • faster
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Subsets of SQL

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What do you mean by DBMS? What are its different types?

A

Hierarchial

Network

Relational

Object-Oriented

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What do you mean by table and field in SQL?

A

Table refers to a collection of data in an organised manner in form of rows and columns

Field refers to the number of columns in a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are joins in SQL?

A

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there.

Inner Join

Full Join

Left Join

Right Join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

CHAR and VARCHAR2 datatype difference

A

Char is used for strings of fixed length

Varchar2 is used for character strings of variable length

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a Primary key?

A

A specific choice of a minimal set of attributes that uniquely specify a tuple in a relation.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are constraints?

A

Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement.

NOT NULL

UNIQUE

CHECK

DEFAULT

INDEX

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the difference between SQL and MySQL?

A

Structured Query Language

MySQL is an open-source relational database management system that works on many platforms. It provides multi-user access to support many storage engines and is backed by Oracle.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is a unique key?

A

Uniquely identifies a single row in the table.

Multiple values allowed per table.

Null values are allowed.

Duplicate values are not allowed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a foreign key?

A

Foreign key maintains referential integrity by enforcing a link between the data in two tables.

The foreign key in the child table references the primary key in the parent table.

The foreign key constraint prevents actions that would destroy links between the child and parent tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What do you mean by data integrity?

A

Accuracy of data

Consistency of data

Integrity Constraints to enforce buisness rules on data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the difference between clustered and non clustered index in SQL?

A

Clustered Index

  • clustered index is used for easy retrieval of data from the database and is faster
  • clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index
  • one table can only have one clustered index

Non Clustered Index

  • non-clustered index is used for easy retrieval of data from the database and is slower
  • non-clustered index does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching
  • one table can have many non-clustered indexes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Write a SQL query to display the current date?

A

GetDate()

SELECT GETDATE();

Result: ‘2019-03-18 18:17:26.160’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the different type of joins?

A

Inner Join

Full Join

Left Join

Right Join

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What do you mean by Denormalization?

A
  • Refers to a technique which is used to access data from higher to lower forms of a database.
  • Increase the performance of the entire infrastructure as it introduces redundancy into a table.
  • Adds the redundant data into a table by incorporating database queries that combine data from various tables into a single table.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are Entities?

A

A person, place, or thing about which data can be stored in a database. Tables store data that represents one type of entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What are relationships?

A

Relation or links between entities that have something to do with each other.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is an index?

A
  • Performance tuning method
  • Allows faster retrieval of records from the table
  • Creates an entry for each value
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Explain different types of index.

A

Unique Index

  • This index does not allow the field to have duplicate values if the column is unique index. If a primary key is defined, a unique index can be applied automatically.

Clustered Index

  • This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.

Non-clustered Index

  • Non-clustered index does not alter the physical order of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is Normalization and what are the advantages of it?

A
  • Better database organization
  • more tables with smaller rows
  • efficient data access
  • greater flexibility for queries
  • quickly find the information
  • easier to implement security
  • allows easy modification
  • reduction of redundant and duplicate data
  • more compact database
  • ensure consistent data after modification
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is the difference between DROP and TRUNCATE commands?

A

DROP removes a table and it cannot be rolled back from the database

TRUNCATE removes all rows from the table and cannot be rolled back into the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Explain the different types of Normalization.

A

divides larger tables into smaller tables and links them together

1 NF

  • each table cell should contain a single value
  • each record needs to be unique

2 NF

  • Be in 1 NF
  • Single column primary key

3 NF

  • Be in 2 NF
  • has no transitive functional dependencies

BCNF

Stricter than 3 NF

A table is in BCNF if every functional dependency X → Y, X is the super key of the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is ACID property in a database?

A

Atomicity

  • Transactions are all or nothing

Consistency

  • Only valid data is saved

Isolation

  • Transactions do not affect each other

Durability

  • Written data will not be lost
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What do you mean by “Trigger” in SQL?

A

Trigger in SQL are a special type of stored procedies that are defined to execute automatically in plasce or after data modifications. It allows you to execute a batch of code when an insert, update, or any other query is executed against a specific table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What are the different operators available in SQL?

A

Arithmetic

Bitwise

Comparison

Compound

Logical

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What is the difference between cross join and natural join?

A

Cross join produces the cross product or Cartesian product of two tables

Natural join based on all the columns having the same name and data types in both the tables.

28
Q

What is a subquery in SQL?

A

A query inside another query where a query is defined to retrieve data or information back from the database.

Subqueries are always executed first and the result of the subquery is passed on to the main query.

29
Q

What are the different types of a subquery?

A

Correlated Subquery: These are queries which select the data from a table referenced in the outer query. It is not considered as an independent query as it refers to another table and refers the column in a table.

Non-correlated subquery: This query is an independent query where the output of subquery is substituted in the main query.

30
Q

Can you list the ways to get the count of records in a table?

A

SELECT * FROM table1;

SELECT COUNT(*) FROM table1;

SELECT rows FROM sysindexes

WHERE id = OBJECT_ID(table1) AND indid < 2

31
Q

Write s SQL query to find the names of employees that begin with ‘A’?

A

SELECT * FROM Table_name WHERE EmpName LIKE ‘A%’

32
Q

Write a SQL query to get the third highest salary of an employee from employee_table?

A

SELECT TOP 1 salary

FROM (

SELECT TOP 3 salary

FROM employee_table

ORDER BY salary DESC) AS emp

ORDER BY salary ASC;

33
Q

What is the need for group functions in SQL?

A

Group functions work on the set of rows and returns one result per group.

Some of the commonly used group functions are:

AVG

COUNT

MAX

MIN

SUM

VARIANCE

34
Q

What is a Relationship and what are the different type of relationships?

A

Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database.

One to One

Many to One

One to Many

Self-Referencing

35
Q

How can you insert NULL values in a column while inserting the data?

A

Implicitly by omitting column from column list

Explicitly by specifying NULL keyword in the VALUES clause

36
Q

What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

A

BETWEEN: Used to display rows based on a range of values in a row.

SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50

IN: Used to cdheck for values contained in a specific set of values.

SELECT * FROM students WHERE ROLL_NO IN (8,15,25);

37
Q

Why are SQL functions used?

A

SQL functions are used for the following purposes.

(calculate, convery, format, modify, and manipulate)

To perform some calculations on the data

To convert the data types

To format dates and numbers

To modify individual data items

To manipulate the output

38
Q

What is the need of MERGE statement?

A

Allows conditional update or insertion of data into a table.

It performs an UPDATE if a row exists, or an INSERT if the row does not exist.

39
Q

What do you mean by recursive stored procedure?

A

Recursive stored procedure refers to a stored procedure which calls by itself uintil it reaches some boundary condition.

This recursive function or procedure helps the programmers to use the same set of code n number of times.

40
Q

What is a CLAUSE in SQL?

A

SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.

Example:

WHERE & HAVING clause

41
Q

What is the difference between ‘HAVING’ clause and a ‘WHERE’ clause?

A

HAVING clause: Can be used only with SELECT statement. It is usually used in a GROUP BY clause.

WHERE clause: applied to each row before they are a part of the GROUP BY function in a query.

42
Q

List the ways in which Dynamic SQL can be executed

A
  1. Write a query with parameters
  2. Using EXEC
  3. Using sp_executesql
43
Q

What does a constraint do?

A

Constrants are the representation of a column to enforce data entity and consistency.

44
Q

What are the various levels of constraints?

A
  1. Column Level Constraint
  2. Table Level Constraint
45
Q

How can you fetch common records from two tables?

A

INTERSECT

SELECT column1, column2 …

FROM table_names

WHERE condition

INTERSECT

SELECT column1, column2 …

FROM table_names

WHERE condition

Example

SELECT studentID FROM student

INTERSECT

SELECT StudentID FROM Exam

46
Q

List some manipulation functions in SQL

A

LOWER(‘string’)

UPPER(‘string’)

this function returns the string with the first letter in uppercase and the rest of the letters in lowercase.

INITCAP(‘string’)

47
Q

What are the different set operators available in SQL?

A

UNION

Combines rows from both the queries

INTERSECT

Keeps only those rows which are common in both queries

MINUS

Keeps rows from the left query which are not included in the right query

48
Q

What is an ALIAS command?

A

Use the AS keyword in conjunction with the WHERE clause

SELECT emp.empID, dept.Result FROM employee emp, department AS dept WHERE emp.empID=dept.empID

49
Q

What are aggregate and scalar functions?

A

Aggregate functions: used to evaluate mathematical calculation and returns a single value. These calculations are done from the column in a table.

Example: MAX(), COUNT()

Scalar functions return a single value based on the input value.

Example: UCASE(), NOW()

50
Q

How can you fetch alternate records from a table?

A

You can fetch alternate records i.e. both odd and even row numbers.

Example: to display even numbers, use the following command:

SELECT studentId FROM (SELECT rowno, studentId FROM student) where mod(rowno, 2)=0

Now, to display odd numbers:

SELECT studentId FROM (SELECT rowno, studentId FROM student) WHERE mod(rowno,2)=1

51
Q

Name the operator which is used in the query for pattern matching?

A

LIKE operator is used for pattern matching.

% - it matches zero or more characters.

Example:

SELECT * FROM students WHERE studentname LIKE ‘a%’

_ - It matches exactly one character.

Example:

SELECT * FROM student WHERE studentname LIKE ‘abc_’

52
Q

How can you select unique records from a table?

A

You can select unique records from a table by using the DISTINCT keyword.

Example:

SELECT DISTINCT studentID FROM Student;

53
Q

How can you fet6ch first 5 characters of the string?

A

Example:

SELECT SUBSTRING(StudentName,1,5) AS StudentName FROM student

SELECT RIGHT(StudentName,5) AS studentName FROM student

54
Q

What is the main difference between SQL and PL/SQL?

A

SQL is a query language that allows you to issue a single query or execute a single INSERT/UPDATE/DELETE

PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as SELECT/INSERT/UPDATE/DELETE

55
Q

What is a View?

A

A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.

56
Q

What are Views used for?

A

A view refers to a logical snapshot based on a table or another view.

  • Restricting access to data
  • Making complex queries simple
  • Ensuring data independence
  • Providing different views of some data
57
Q

What is a stored procedure?

A

A function which consists of many SQL statements to access the database system.

Several SQL statements are consolidated into a stored procedure and are executed whenever and wherever required.

This saves time and avoid writing code again and again.

58
Q

List some advantages and disadvantages of stored procedures.

A

Advantage: A stored procedure can be used as a modular programming which means create one, store, and call for several times whenever it is requred. This suppiorts faster execution. It also reduces network traffic and provides better security to the data.

Disadvantages: can be executed only in the database and utilizes more memory in the database server.

59
Q

List all the types of user-defined functions.

A

Scalar functions

Multi-statement valued functions

Inline Table-valued functions

60
Q

What do you mean by collation?

A

Collation is defined as a set of rules that determine how data can be sorted as well as compared.

Character data is storted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width, etc.

61
Q

What are Local and Global variables?

A

Local variables: these variables can be used or exist only inside the function. These variables are not used or referred by any other function.

Global variables: these variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called.

62
Q

What is auto Increment in SQL?

A

Allows the user to create a unique number to get generated whenever a new record is inserted into the table.

This keyword is usually required whenever PRIMARY KEY is used.

AUTO INCREMENT keyword can be used in Oracle and IDENTIFY keyword can be used in SQL SERVER.

63
Q

What is a Datawarehouse?

A

Datawarehouse refers to a central repository of data where the data is assembled from multiple sources of information.

Those data are consolidated, transformed and made available for the mining as well as online processing.

Warehouse data also has a subset of data called Data Marts.

64
Q

What are the different authentication mdoes in SQL Server? How can it be changed?

A

Windows Mode | Mixed Mode

  1. Click Start -> Programs -> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
  2. Select the server from the Tools menu.
  3. Select SQL Server Conficuration Properties, and choose the Security page.
65
Q

What are STUFF and REPLACE function?

A

STUFF function:

This function is used to overwrite exising character or inserts a string into another string.

STUFF(string_expression, start, length, replacement_characters)

REPLACE function:

This function is used to replace the existing characters of all the occurrances.

REPLACE(string_expression, search_string, replacement_string)

66
Q
A

SQL Interview Questions (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions