Brainscape's Knowledge GenomeTM

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


See full index

SDET Interview 2024 Set > SQL Questions > Flashcards

SQL Questions Flashcards

1
Q

How would you rate yourself in SQL? (from 1 to 10)

  • 1-4: Beginner
  • 5-6: Intermediate
  • 6+: Advanced
A

Answer 1: From the SDET point of view I would consider my SQL skills to be at an 8 out of 10.

Answer 2: I would rate myself 6 out 10 since I am not a database administrator but can perform the necessary queries as an SDET.

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

What is a Database?

A

A database is a structured collection of data that is organized and stored in a way that allows for efficient retrieval, management, and manipulation of that data.

Databases are used to store and manage large volumes of information, making it easy to access, update, and analyze the data.

They are a fundamental component of information systems and are used in a wide range of applications, from simple personal record-keeping to large-scale enterprise systems.

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

What is RDBMS?
How is it different from DBMS?

A

RDBMS ⇒ Relational Database Management System.

Relational Databases are structured with tables that are connected through Primary and Foreign key relationships.

Employees
employee_id | name| SSN| DOB| email| phone number| department_id
1 | Muneer | XXXX |values ……
2 | Amro ……….

Departments
department_id| department_name | Department_start_date | department_boss

If the true intention of the employees table is to store data related to employees then only attributes related to employees should be there.

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

What does SQL stand for?

A

SQL ⇒ Structured Query Language used in programming and designed for managing and manipulating relational databases.

SQL is commonly used for tasks such as querying data from databases, inserting, updating, and deleting records, creating and modifying database schemas (tables, indexes, views, etc.), and managing permissions and security within a database system.

It provides a standardized way of interacting with databases across different platforms and is widely used in the field of database management and data analysis.

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

What does Relational mean when we are referring to Relational Database?

A

SHORT ANSWER: Relational Databases are structured with tables connected through Primary and foreign key relationships.

EXTENDED ANSWER: In the context of a Relational Database, the term “Relational” refers to the way data is organized within the database and the principles upon which the database management system operates.

A Relational Database organizes data into tables, where each table consists of rows and columns.

The relationships between tables are established through common fields, typically referred to as keys, which are used to connect data between different tables.

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

Are you familiar with any Constraints in SQL?

A

Constraint ⇒ Rules for relational databases.

Primary Key ⇒ A unique identifier in a table that helps identify a specific row for a given table. Uniquely identifies a record in the table.

Foreign Key ⇒ A foreign is a column within a table that is a primary key in another table.

Unique/composite Key ⇒ A column in a table that is not the primary key but only has unique values.

NOT NULL ⇒ There should always be a value defined.

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

What TOOLS are you using to work with your database?

A
  • DB Viewer ⇒ MySQL Workbench
  • DB server ⇒ MySQL

Currently in my company the application database is a MySQL based server.

I use MySQL Workbench to connect to my application database and run queries.

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

What is a Primary Key?

A

Primary Key ⇒ A unique identifier in a table that helps identify a specific row for a given table.

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

What is a Foreign Key?

A

Foreign Key ⇒ A foreign is a column within a table that is a primary key in another table.

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

What is a Unique key?

A

Unique/composite Key ⇒ A column in a table that is not the primary key but only has unique values.

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

What is a JOIN?

What are the different types of JOIN?

A

What is a JOIN? ⇒ The process of combining data between 2 or more tables.

Types of JOIN:
* INNER
* LEFT
* RIGHT
* FULL
* SELF

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

Have you heard of an INDEX before?

A

SHORT: Indexes are special items added to database tables to allow faster retrieval of data.

LONG ANSWER: Yes, I’m familiar with indexes in SQL. In a relational database management system (RDBMS), an index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional space and slower inserts, updates, and deletes. Indexes are created on one or more columns of a table and are used to quickly locate rows that match a given search condition, typically by reducing the number of rows that need to be examined during query execution.

Types of Indexes:

  • Single-Column Index: An index created on a single column of a table.
  • Composite Index: An index created on multiple columns of a table, allowing for more complex query optimizations.
  • Unique Index: An index that enforces uniqueness on the indexed column(s), preventing duplicate values.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a Subquery in SQL?

A

SHORT: A query within a query.

LOND: A subquery, also known as an inner query or nested query, is a query nested within another SQL query. Subqueries are enclosed within parentheses and are used to retrieve data that will be used as a condition or value in the outer query.

Subqueries can be used in various parts of an SQL statement, such as the SELECT, FROM, WHERE, HAVING, and JOIN clauses. They provide a powerful way to perform complex data manipulations and filtering operations by allowing you to break down a problem into smaller, more manageable parts.

Example:

When you need another query to filter a specific value that becomes an input to your main query.

SELECT *
FROM table
WHERE column = (Inner query);

Need to find the employees that make more than the average salary?

SELECT employee_name, phone, email
FROM employees
WHERE avg salary > (SELECT avg salary from employees);

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

What is UNION? What is the difference between UNION and UNION ALL?

A

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
ANSWER 1:

UNION ⇒ When you combine the output between multiple independent select statements. (removes duplicate)

UNION ALL => (output will be duplicates and non duplicates)

Rules:
1. Same number of columns in each query.
2. Order of the column and data type needs to match.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
ANSWER 2: The result of a UNION operation includes all distinct rows from both SELECT queries, and duplicate rows are eliminated.

On the other hand, UNION ALL also combines the results of two or more SELECT queries into a single result set, but it does not remove duplicate rows. It includes all rows from all SELECT queries, regardless of whether they are duplicates.

In summary, UNION combines the results of multiple SELECT queries into a single result set, removing duplicate rows, while UNION ALL combines the results without removing duplicates. The choice between UNION and UNION ALL depends on the specific requirements of the query and whether duplicate rows need to be retained or eliminated.

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

Have you heard of a STORED procedure before?

A

A STORED procedure is a custom database function that is created for a specific process. Developers or Database Engineers create STORED procedures.

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

Can you JOIN more than 2 tables?

When you are joining, what are you looking for within the tables to ensure you can join them?

A

- YES!

JOIN criteria:

  1. Primary Key and foreign key relationship.
  2. Common column.
  3. Common data type.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are the Usages of SQL?

A
  • Creating new databases
  • Creating new tables in a database
  • Inserting records in a database
  • Updating records in a database
  • Deleting records from a database
  • Retrieving data from a database
  • Executing queries against a database
  • Creating stored procedures in a database
  • Creating views in a database
  • Setting permissions on tables, procedures, and views
  • …and many other data storage operations
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What are important SQL Language Elements?

A
  • Identifiers: Names of Database objects such as tables, views, columns, and databases etc…
  • Data Types: Define the type of data that is contained by a column.
  • Constants: Symbols that represent specific data types.
  • Operators: Perform Arithmetic, Comparison, and Logical Operations.
  • Functions: Built-in Functions to perform specific operations.
  • Clauses: Constituent components of statements and queries.
  • Expressions: Produce either scalar values, or tables consisting of columns and rows of data.
  • Queries: Retrieve the data based on specific criteria. This is an important element of SQL.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the Sub Sets of SQL?

A
  • DDL - Data Definition Language
  • DML - Data Manipulation Language
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is Data Definition Language?

A

Data Definition Language (DDL) - allows us to create, alter, and delete database objects such as schemas, tables, views, sequences, catalogs, indexes and aliases.

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

What is Data Manipulation Language?

A

Data Manipulation Language (DML) is a language which enables users to access and manipulate data.

DML is used to Perform below operations:
* Insertion of data into the database
* Retrieval of data from the database
* Updating data in the database
* Deletion of data in the database

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

What is Database?

A

A Database (DB) is a systematic collection of data, Databases support storage and manipulation of data. Using a databases makes data management easy.

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

What is Table?

A

A Table in a Relational Database is a predefined format of rows and columns that define an entity.

Each column contains a different type of attribute and each row corresponds to
a single record.

Each table is provided with a name.

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

What is DBMS?

A

A DataBase Management System (DBMS) is software designed to assist in maintaining and utilizing large collection of data.

The alternative to using a DBMS is to store the data in files and write application-specific code to manage it.

Using a DBMS to manage data has many advantages like:

  • Data independence
  • Efficient data access
  • Data integrity and security
  • Data administration
  • Concurrent access and data recovery
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is MySQL?

A

MySQL is open source Database Management System, developed by Swedish company MySQL AB.

MySQL supports many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.

MySQL has free and paid versions, depending on its usage (non- commercial/commercial) and features.

MySQL comes with a very fast, multi-threaded, multi-user, and robust SQL database server.

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

What are the categories of Operators available in SQL?

A
  • Arithmetic operators
  • Comparison operators
  • Logical operators
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What are Arithmetic operators in SQL?

A

Arithmetic Operator in SQL:

  • + (Addition) - Adds values
  • - (Subtraction) - Subtracts Right side value from Left side value
  • * (Multiplication) - Multiplies values on either side of the operator
  • / (Division) - Divides left hand operand by right hand operand
  • % (Modulus) - Divides left hand operand by right hand operand and returns remainder
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What are Comparison operators in SQL?

A

For example we have two variables:

  • x = 1
  • y = 2

Comparisor Operator and it’s Logical results:

  • = (x = y) is False
  • != (x != y) is True
  • <> (x <> y) is True
  • > (x > y) is False
  • < (x < y) is True
  • >= (x >= y) is False
  • <= (x <= y) is True
  • !< (x !< y) is False
  • !> (x !> y) is True
29
Q

What are Logical operators in SQL?

A

  • NOT Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE
  • AND Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE
  • OR Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE.
30
Q

What are Important Data Types in SQL?

A

Data Type … Syntax

character … char(x)
integer … integer
numeric … numeric(p,s)
decimal … decimal(p,s)
float … float(p)
date … date
time … time
character varying … varchar2(x)
bit … bit(x)
real … real
smallint … smallint

31
Q

How to Create a Database?

A

The SQL CREATE DATABASE statement is used to create new SQL database.

Syntax:
CREATE DATABASE DatabaseName;

Example:
SQL> CREATE DATABASE TestData;

32
Q

How to Delete a Database?

A

Using DROP DATABASE statement we can delete any existing Database

Syntax:
DROP DATABASE DatabaseName;

Example:
SQL> DROP DATABASE TestData;

33
Q

How to Select a Database?

A

USE statement is used to select any existing database in SQL

Syntax:
USE DatabaseName;

Example:
SQL> USE TestData;

34
Q

How to View all existing Databases list?

A

SQL> SHOW DATABASES;

35
Q

How to Create a Table?

A

*CREATE TABLE tablename (

column1 datatype,

column2 datatype,

column3 datatype,

…..

columnN datatype,

PRIMARY KEY ( one or more columns )

);*

36
Q

How to Delete a Table?

A

Using Drop Table we can delete a Table

Syntax:
DROP TABLE table_name;

37
Q

How to Add New Record into a Table?

A

Using INSERT INTO statement, we can insert new rows

Syntax:
INSERT INTO TABLENAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN)

38
Q

How to Fetch Data from a Database Table?

A

Using SELECT Statement, we can fetch data from a Database Table

Syntax:

SELECT column1, column2, columnN FROM tablename;

Or

SELECT FROM tablename;

39
Q

Explain about IN Operator?

A

The IN operator implements comparison to a list of values, that is, it tests whether a value matches any value in a list of values.

IN comparisons have the following general format:

value-1 [NOT] IN ( value-2 [, value-3] … )

This comparison tests if value-1 matches value-2 or matches value-3, and so on. It is equivalent to the following logical predicate:

value-1 = value-2 [ OR value-1 = value-3 ] …

40
Q

Explain about FROM clause in SQL?

A

The FROM clause always follows the SELECT clause. It lists the tables accessed by the query.

Example:
SELECT FROM s

When the FROM List contains multiple tables, commas separate the table names.

Example:
SELECT sp.*, city
FROM sp, s
WHERE sp.sno = s.sno

When the From List has multiple tables, they must be joined together.

41
Q

What is the difference between GROUP BY and ORDER BY?

A

GROUP BY controls the presentation of the rows,

ORDER BY controls the presentation of the columns for the results of the SELECT statement.

42
Q

What is a Subselect?

Is it different from a Nested Select?

A

A Subselect is a select which works in conjunction with another select.

A Nested Select is a kind of subselect where the INNER SELECT passes to the WHERE criteria for the OUTER SELECT.

43
Q

Can you have a Primary key and a Foreign Key on the same table?

A

Yes, Primary key and Foreign Key can be present in the same table.

44
Q

What are most important DDL Commands in SQL?

A

CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table

DROP TABLE - deletes a database table

45
Q

What are the Operators used in SELECT statements?

A
  • = - Equal
  • <> or != - Not equal
  • > - Greater than
  • < - Less than
  • >= - Greater than or equal
  • <= - Less than or equal
  • BETWEEN - Between an inclusive range
  • LIKE - Search for a pattern
46
Q

How to INSERT Values into Tables?

A

INSERT INTO tablename VALUES (value1, value2,….)

INSERT INTO tablename (column1, column2,…) VALUES (value1, value2,….)

47
Q

How to Update a Column Name?

A

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

48
Q

How to Delete Columns, Rows?

A
  • Delete a particular column:

DELETE FROM tablename
WHERE columnname = somevalue

  • Delete All Rows:

DELETE FROM tablename

or

DELETE * FROM tablename

49
Q

Give an usage for BETWEEN … AND?

A

SELECT columnname
FROM tablename
WHERE columnname
BETWEEN value1 AND value2

  • The values can be numbers, text, or dates.
50
Q

What operator performs pattern matching?

A

Operator - LIKE

51
Q

What is the use of the DROP option in the ALTER TABLE command?

A

It is used to drop constraints specified on the table.

52
Q

What operator tests column for the absence of data?

A

it is NULL operator

53
Q

What is the use of DESC in SQL?

A

DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.

Explanation:

The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

54
Q

What are the wildcards used for pattern matching?

A

_ for single character substitution

% for multi-character substitution.

55
Q

What is difference between TRUNCATE & DELETE?

A

TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE.

|

DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.

56
Q

What is the SUBQUERY?

A

SUBQUERY is a query whose return values are used in filtering conditions of the main query.

57
Q

Explain UNION, and UNION ALL?

A

UNION - returns all distinct rows selected by either query.

|

UNION ALL - returns all rows selected by either query, including all duplicates.

58
Q

Do you know SQL?

A

BEST ANSWER:

● Yes, I am very comfortable with writing SQL Queries and DDL and DML commands.

● Currently working with Oracle database that is running in AMAZON CLOUD SERVER.

● DDL (Data definition language) : CREATE , ALTER, DROP, TRUNCATE..

● DML (Data manipulation language): SELECT, DELETE, INSERT, UPDATE

59
Q

What SQL means?

A

● Structured Query Language - used for managing and manipulating data in DataBase.

|

● Provide statements for a variety of tasks:
* Querying data;
* Inserting, updating, deleting rows in a table;
* Creating, replacing, altering, and dropping objects;
* Controlling access to the database and its objects;
* Database consistency and integrity.

60
Q

What are the categories of SQL Statements?

A
  1. DML (Data Manipulation Language) ● DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.
  2. DDL (Data Definition Language) ● DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.
  3. DCL (Data Control Language) ● DCL statements control the level of access that users have on database objects.
  4. TCL (Transaction Control Language) ● TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.
61
Q

Tell me about TCL?

A
  • SQL language is divided into four types of primary language statements: DML, DDL, DCL and TCL.
  • Using these statements, we can define the structure of a database by creating and altering database objects, and we can manipulate data in a table through updates or deletions.
  • We also can control which user can read/write data or manage transactions to create a single unit of work.
62
Q

What is SQL clause?

A

SELECT and FROM.

63
Q
  1. What kind of Database testing are you doing?
A
  • I am mostly doing Database validations.
  • I make changes or insert data (create loan) in the front end and validate in the database. Data in frontend matches the
    DB.
  • I also make changes using RESTapi and verify that changes are successful in Database as well.
  • I also support DB migration process. My code connects to Sybase (legacy database) using JDBC then connects to Oracle (NEW DB) then compare records to make sure data was migrated
64
Q

What is RDBMS?

A

A: RDMBS - Relational Database Management System where Data is organized into tables that are related to each other.

|

Q: How are they related?

A: Primary Key (unique and not NULL) and Foreign Key (duplicate and NULL).

|

Q: What type of database system you have expertise with?
A: RDBMS, such as MySQL and Oracle

65
Q

What Data types in SQL you know?

A

● number
● integers
● char → char(20): 20 years spaces are taken from memory
● varchar → varchar(30): 5 spaces from memory varchar2
● boolean
● date
● currency

66
Q

Capabilities for SQL select statements

A
  • Projection → Select the columns in a table that are returned by a query

~

  • Selection → Selects the rows in a table that are returned by a query

~

  • Join → Brings together data that is stored in different tables by specifying the link between them
67
Q

DML (Data Manipulation Language) vs DDL (Data Definition Language)

A

DML command actions can be restored.

COMMANDS:

● SELECT from tablename; (read)
● INSERT into tablename values (…); (add)
● UPDATE tablename SET value WHERE location;
● DELETE from tablename WHERE location; (rows)
● MERGE

~

DDL command actions cannot be restored / undone.

COMMANDS:

● CREATE table tablename (column1, column2 …);
● ALTER table tablename modify value;
● TRUNCATE table tablename; (delete whole table data)
● DROP TABLE (delete whole table with structure)
● RENAME
● COMMENT

68
Q

What types of JOIN you know?

A
  • INNER JOIN - is used when retrieving data from multiple tables and will return only matching data
  • LEFT OUTER JOIN - is used when retrieving data from multiple tables and will return left table and any matching right table records.
  • RIGHT OUTER JOIN - is used when retrieving data from multiple tables and will return right table and any matching left table records.
  • FULL OUTER JOIN - is used when retrieving data from multiple tables and will return both table records, matching and non-matching
69
Q
A

SDET Interview 2024 Set (4 decks)

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