Brainscape's Knowledge GenomeTM

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


See full index

DS+Python+SQL interview questions > SQL Interview Questions > Flashcards

SQL Interview Questions Flashcards

source: https://www.edureka.co/blog/interview-questions/sql-interview-questions

1
Q

ACID

A

ACID (Atomicity, Consistency, Isolation, Durability) - It is used to ensure that the data transactions are processed reliably in a database system.

  • Atomicity - refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
  • Consistency - ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
  • Isolation - The main goal of isolation is concurrency control.
  • Durability - means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Aggregate vs Scalar functions

A

Aggregate functions are used to evaluate mathematical calculation and returns a single value. These calculations are done from the columns in a table. For example- MAX(), COUNT(), SUM().

Scalar functions return a single value based on the input value. For example – UPPER(), NOW().

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

ALIAS

A
  • ALIAS name can be given to any table or a column.
  • This alias name can be referred in WHERE clause to identify a particular table or a column.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Authentication modes. How can it be changed?

A

Windows mode and Mixed Mode

  • Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
  • Then select the server from the Tools menu.
  • Select SQL Server Configuration Properties, and choose the Security page.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Auto Increment

A
  • automatically generate unique numbers when inserting new record to table.
  • usually use auto increment column as primary key
  • automatically increase value by 1 for each record, use parameters to change starting and increment values
  • AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

BETWEEN vs IN

A
  • BETWEEN is a range
  • IN check if value exist in given set
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

case manipulation functions

A

* LOWER: takes a string as an argument and returns it by converting it into lower case. LOWER(‘string’)

* UPPER: takes a string as an argument and returns it by converting it into uppercase. UPPER(‘string’)

* INITCAP: returns the string with the first letter in uppercase and rest of the letters in lowercase. INITCAP(‘string’)

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

CHAR vs VARCHAR2

A
  • both character datatypes
  • CHAR uses a fixed length (“abc” -> CHAR(10) -> “abc “ (10 character)
  • VARCHAR2 can store any length up to the limit (“abc” -> VARCHAR(10) -> “abc” (3 character)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

CLAUSE in SQL

A
  • helps to filter/limit the rows from the entire set of records.
  • WHERE, HAVING clause.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Clustered vs Non-clustered index

A

Clustered Index: * Physically stored the rows on the dick in the same order as the index * there can only be 1 cluster * faster to read from clustered index Non-Clustered Index: * a list that points to physical rows * can have many * takes time to write therefore slower than clustered index

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

Collation

A

Set of rules that determine how data can be sorted as well as compared.

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

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

types of Collation Sensitivity

A
  • Case Sensitivity: A and a and B and b.
  • Kana Sensitivity: Japanese Kana characters.
  • Width Sensitivity: Single byte character and double-byte character.
  • Accent Sensitivity.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can you fetch common records from two tables?

A
  • fetch common records from two tables using INTERSECT
  • example:
    SELECT studentID FROM student
    INTERSECT
    SELECT StudentID FROM Exam
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the various levels of constraints?

A
  • column level constraint
  • table level constraint
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Constraints

A
  • use to specify limits/rules when creating or altering tables
  • list of constraints:
    • NOT NULL
    • CHECK
    • DEFAULT
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

CROSS JOIN vs NATURAL JOIN

A
  • CROSS JOIN - returns cross product of two tables
  • JOIN - returns data from both table based on common column
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Current date

A

* SELECT CURRENT_DATE;

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

Data Integrity

A

* refers to accuracy and consistency of data. * constraints helps with integrity when entered * business rules reinforcement

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

Datawarehouse

A
  • Central repository of data where the data is assembled from multiple sources of information.
  • Data are consolidated, transformed and made available for the mining as well as online processing.
  • Warehouse data also have a subset of data called Data Marts.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is DBMS? What are its different types?

A
  • Database Management System (DBMS) is a software allowing interaction with the data. (PostGresSql, MS SQL, Oracle, etc)
  • 2 types of DBMS:
    • Relational Database Management System (RDBMS) - structured relational tables (MySQL, SQL etc)
    • Non-Relational Database Management System - unstructured database (Mongo)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

DELETE vs TRUNCATE

A

DELETE:

  • delete a row in a table
  • can rollback data after deletion
  • uses DML command (Data Manipulation Language)
  • slower than truncate

TRUNCATE:

  • delete all rows in a table
  • cannot rollback
  • uses DDL command (Data Definition Language)
  • faster than delete
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Denormalization

A
  • Opposite of normalization where it combines smaller tables into larger one.
  • it causes redundancy
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

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

DROP vs TRUNCATE

A

DROP

  • * Deletes the table
  • * cannot be rollback

TRUNCATE

  • * Removes all the rows from the table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

List the ways in which Dynamic SQL can be executed?

A
  • Write a query with parameters.
  • Using EXEC.
  • Using sp_executesql.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Entities and Relationships

A
  • Entities are tables or data stored about a people, places or things (customer table, bank transaction)
  • Relationships are links between entities (customer table and bank transaction are linked by customer id field)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

FOREIGN key

A

* link between two tables * references a primary key in a different table

28
Q

Why are SQL functions used?

A

SQL functions are used for the following purposes:

  • To perform some calculations on the data
  • To modify individual data items
  • To manipulate the output
  • To format dates and numbers
  • To convert the data types
29
Q

Types of User-defined functions

A
  • Scalar Functions - return unit
  • Inline Table-valued functions - returns table
  • Multi-statement valued functions - returns table
30
Q

GROUP BY

A

* needs an aggregate function: AVG, COUNT, MAX, MIN, SUM, VARIANCE, FIRST, LAST.

31
Q

HAVING vs WHERE

A

* HAVING Clause is only used with the GROUP BY function * WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query

32
Q

Index

A
  • performance tuning method for faster data retrieval/reading
33
Q

Different types of index

A

Unique index:

  • no duplicate values (primary key for example)

Clustered Index:

  • reorders the physical table based on key values
  • 1 clustered index per table

Non-Clustered Index:

  • doesn’t reorder the physical table
  • maintain logical order of data
  • can have many non-clustered indices
34
Q

JOINs

A
  • Joins is a way of combining row from 2+ tables/sources into 1 place based on common/related column
  • types of joins:
    • INNER JOIN - returns what both tables have in common
    • LEFT JOIN - inner join + left table data
    • RIGHT JOIN - inner join + right table data
    • FULL JOIN - all data from both tables
35
Q

JOINS types

A
  • INNER JOIN - returns what both tables have in common
  • LEFT JOIN - inner join + left table data
  • RIGHT JOIN - inner join + right table data
  • FULL JOIN - all data from both tables
36
Q

MERGE

A
  • Allows conditional update or insertion and deletion of data in a table.
  • It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
  • Format:
    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;
  • example:
    MERGE sales.category t USING sales.category_staging s
    ON (s.category_id = t.category_id)
    WHEN MATCHED
    THEN UPDATE SET
    t.category_name = s.category_name, t.amount = s.amount
    WHEN NOT MATCHED BY TARGET
    THEN INSERT (category_id, category_name, amount)
    VALUES (s.category_id, s.category_name, s.amount)
    WHEN NOT MATCHED BY SOURCE
    THEN DELETE;
37
Q

Normalization

A

Organize data to avoid duplicate and redundancy

  • 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
38
Q

Normalization types

A
  • 1st Normal Form (1NF) - 1 entry per cell, no multiple values
  • 2nd Normal Form (2NF) - non-key columns are dependent on the primary key
  • 3rd Normal Form (3NF) - Dependent solely on the primary key and no other non-key (supporting) column value.
39
Q

NULL vs zero/space values

A

* NULL values means no data (unknown, unavailable, N/A) * zero is actually a number (so it’s a value and not null) * blank space is actually a character (so not null)

40
Q

insert NULL values

A
  • Implicitly by omitting column from column list.
  • Explicitly by specifying NULL keyword in the VALUES clause
41
Q

operator which is used in the query for pattern matching

A

LIKE operator is used for pattern matching

  • % – It matches zero or more characters
  • _ (Underscore) – it matches exactly one character
42
Q

Operators

A
  • Arithmetic (+ - / *)
  • Logical (AND OR NOT)
  • Comparison(> < !=)
43
Q

different set operators

A

Union, Intersect or Minus operators.

44
Q

Primary key

A

A constraint which is:

  • can’t be null (null values not allowed)
  • unique (no duplicate)
  • can be a column or a collection of columns
  • 1 per table
45
Q

Recursive Stored Procedure

A

A stored procedure which calls by itself until it reaches some boundary condition.

46
Q

Relationship

A

Relationships are links between entities (customer table and bank transaction are linked by customer id field)
Type of relationships:

  • One to One Relationship.
  • One to Many Relationship.
  • Many to One Relationship.
  • Many to Many Relationship.
47
Q

COUNT records in a table

A
  • SELECT * FROM table;
  • SELECT COUNT(*)
    FROM table;
48
Q

How can you fetch alternate records from a table?

A

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

For example- To display even numbers, use the following command:

SELECT studentId

FROM (Select rowno, studentId from student)

WHERE MOD(rowno,2)=0

49
Q

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

A

SELECT *
FROM Table_name
WHERE EmpName LIKE ‘A%’;

50
Q

How can you fetch first 5 characters of the string?

A

SELECT SUBSTRING(StudentName,1,5) as studentname

FROM student

51
Q

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) ORDER BY salary ASC;

52
Q

What is the difference between SQL and MySQL?

A
  • SQL is language (Structured Query Language)
  • MySQL is RDBMS like SQL Server or PostGresSQL
53
Q

SQL vs PL/SQL

A

SQL - Structured Query Language executing single CRUD command

PL/SQL - Procedural Language SQL from Oracle, full program capable of executing mulitple operations using loops and variables

54
Q

What are the different subsets of SQL?

A
  • DDL (Data Definition Language) - allows you database level operations such as CREATE, ALTER DELETE objects
  • DML (Data Manipulation Language) - allows data level operations such as CRUD in the database
  • DCL (Data Control Language) - allows/control access to database (handles permissions)
55
Q

Stored Procedure

A
  • Saved reusable sql statements
  • think of it as saving you sql statement into a variable or a function(you can pass parameters to it) that you can use later on
56
Q

List advantages and disadvantages of Stored Procedure

A

Advantages:

  • reusable
  • Reduces networks traffic
  • Fast execution
  • Better security to data

Disadvantages:

  • utilizes more memory in the database server because it can only be executed in the database
57
Q

STUFF vs REPLACE

A

STUFF

  • replaces a specify substring with given length and position

REPLACE

  • replaces all occurrences
58
Q

Subquery

A

(query-ception!)

  • It is a nested query, query inside another query
  • they are executed before the main query
  • can be in SELECT UPDATE WHERE,…
59
Q

Type of Subqueries

A

2

  • Correlated - data is from or referenced by main query
  • Non-correlated - independent data from outside source, substituted in main query
60
Q

Table vs Field

A
  • Table - collection of data organized by rows and columns
  • Field - a column in a table
61
Q

Trigger

A
  • special type of Stored Procedures that is executed when data/database modification happens
  • Logon trigger (login into a db)
  • DML trigger (INSERT UPDATE DELETE)
  • DDL trigger (CREATE ALTER DROP)
62
Q

UNIQUE Key

A

* a constraint on column(s) * no duplicate values * no null

63
Q

Local vs Global variables

A

Local variables

  • exist only inside the function
  • not used or referred by any other function

Global variables

  • variables which can be accessed throughout the program
  • cannot be created whenever that function is called
64
Q

View

A
  • Virtual table made of subset data from table(s)
  • Views have up-to-date data
65
Q

What are Views used for?

A
  • Restricting access to data.
  • Making complex queries simple.
  • Ensuring data independence.
  • Providing different views of same data.

DS+Python+SQL interview questions (5 decks)

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