Brainscape's Knowledge GenomeTM

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


See full index

Technical Interview Questions for Full Stack Software Engineers > Skillstorm Intro to Coding - SQL and MySQL > Flashcards

Skillstorm Intro to Coding - SQL and MySQL Flashcards

1
Q

What is normalization in Databases?

A

Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

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

What are the requirements for a relation in a database to be in First Normal Form or 1NF?

A
  • The domain of each attribute (column) contains only indivisible (atomic) values
  • The value of each attribute (column) contains only a single value from that domain

In Other Words

  • No repeating groups (multi-valued column)

==> Only one value per column

  • Table must define a primary key

==> A unique identifier for a row

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

What are the requirements for a relation in a database to be in Second Normal Form or 2NF?

A
  • It is in 1NF.
  • It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation.

In Other Words

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

What are the requirements for a relation in a database to be in Third Normal Form or 3NF?

A
  • It is in 2NF
  • 3NF states that all column references in referenced data that are not dependent on the primary key should be removed

In Other Words

  • No transitive dependencies

==> A column determined by a non-key attribute

  • Includes derived attributes, which should be calculated

==> Your Birthday determines your age

==> Item price times quantity determines line total

==> Sum of line totals determines the order sub-total

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

Who is the father of the relational database model?

A

Edgar F. Codd who released his landmark paper in 1970 “A Relational Model of Data for Large Shared Data Banks”

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

What is a good mnemonic for remembering the normal forms?

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

What is Functional Dependence?

A
  • One value determines another

==> Your birthday determines your age

==> Order number determines the date the order was placed

  • First name does not determine anything
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a composite key in 2NF?

A
  • It’s when two or more column’s come together to determine the primary key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is a Transitive Dependency?

A
  • A column determined by a non-key attribute

In Other Words

  • A transitive dependency in a database is an indirect relationship between values in the same table that causes a functional dependency.
  • By its nature, a transitive dependency requires three or more attributes (or database columns) that have a functional dependency between them, meaning that Column A in a table relies on Column B through an intermediate Column C.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Transitive Dependency: See attached image…

A

Description

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

Partial Dependence: See attached images…

A

Order_Date

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

Assign the right cardinality of each relationship: See attached images…

A

1) Many-to-Many
2) One-to-Many
3) One-to-One

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

Repeating Groups: See attached images…

A

Items_Ordered

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

The SELECT statement is used to return a result set of __________ from one or more ___________.

A

records

tables

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

A WHERE clause specifies that a SQL Data Manipulation Language statement should only affect _____ that meet specified criteria. The WHERE clause is used to extract only those results from a SQL statement, such as: _________, _________, __________, and ___________ statement.

A

rows

SELECT

INSERT

UPDATE

DELETE

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

Comparison (or relational) operators are mathematical symbols used to compare two values. They are used in conditions that compare one expression with another. The result of a comparison can be _________, ________, or _________.

A

TRUE

FALSE

UNKNOWN

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

The BETWEEN operator allows you to select values within a specified _________. These values can be ________, ________, or ________.

A

range

numbers

text

dates

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

The BETWEEN operator is inclusive: _______ and ________ values are included.

A

begin

end

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

The LIKE operator determines if a character string matches a specified ________. The pattern may include regular characters and ________ characters. Use the LIKE operator in the ________ clause of the SELECT, UPDATE, and DELETE statements to filter ________ based on pattern matching.

A

pattern

wildcard

WHERE

rows

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

Retrieve Payments: See attached image…

A

SELECT

FROM

WHERE

BETWEEN, AND

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

Retrieve Country United States: See attached images…

A

SELECT

FROM

WHERE

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

Comparison Operators

A

>

>=

=

!=

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

Several Comparisons: See attached images…

A

SELECT, FROM, WHERE, LIKE, %

SELECT, FROM, WHERE, LIKE, %(Z)%

SELECT, FROM WHERE, LIKE, _

24
Q

Actor’s First and Last Name: See attached image…

A

SELECT

FROM

25
Q

INSERT is a command that is used for inserting one or more ________ into a database table with specified table ________ values.

A

rows

column

26
Q

UPDATE is a statement that changes the data of one or more ________ in a table. Either all the _________ can be updated, or a subset may be chosen using a condition.

A

records

rows

27
Q

DELETE is a statement that removes one or more ________ from a table. A subset may be defined for deletion using a condition, otherwise, all ________ are removed.

A

records

records

28
Q

Remove Rental: See attached images

A

DELETE, FROM

WHERE

29
Q

Create Rows in Actor and City Tables: See attached image…

A

INSERT, INTO

VALUES

INSERT, INTO

VALUES

INSERT, INTO

VALUES

30
Q

Change Values for a Row: See attached image…

A

UPDATE

SET

WHERE

31
Q

When there is no relationship defined between the two tables, use the _______ ________ to return all the ________ in all the ________ listed in the query. Each row in the first ________ is paired with all the ________ in the second table.

A

Cartesian Product

rows

tables

table

rows

32
Q

A JOIN clause is used to combine rows from two or more tables, based on a related ________ between them.

A

column

33
Q

The INNER JOIN is a keyword that selects all _________ from both the tables as long as the _________ satisfies. This ________ will create the result set by combining all ________ from both the ________ where the condition satisfies, i.e., value of the common field will be same.

A

rows

condition

keyword

rows

tables

34
Q

The RIGHT/LEFT JOIN combines data from ________ or more ________. The RIGHT JOIN starts selecting data from the right ________ and matching with the ________ from the left table. The RIGHT JOIN returns a result set that includes all __________ in the right table, whether or not they have matching __________ from the left ________.

A

two

tables

table

rows

rows

rows

table

35
Q

The LEFT JOIN starts selecting data from the _________ __________ and matching with the _________ from the ________ table. The LEFT JOIN returns a result set that includes all rows in the ________ table, whether or not they have matching rows from the __________ table.

A

left table

rows

right

left

right

36
Q

City Records and Related Country Records: See attached images…

A

SELECT

FROM

INNER JOIN

ON

37
Q

Find Customer’s Who Haven’t Rented: See attached images…

A

SELECT

FROM

LEFT JOIN

ON

38
Q

Find Cartesian Product between Film and Actor: See attached images…

A

SELECT

FROM

CROSS JOIN

39
Q

CREATE TABLE is a statement that is used to create tables to store data. You can define integrity constraints such as ________ key, ________ key, and _________ key for the columns while creating the table. The integrity constraints can be defined at the ________ level or _________ level.

A

primary

unique

foreign

column

table

40
Q

ALTER TABLE is a statement that is used to ________, ________, or ________ columns in an existing table. You can also use the ALTER TABLE statement to add and drop various ________ on an existing table.

A

add

delete

modify

constraints

41
Q

DROP TABLE is a statement that is used to remove a table definition and all the ________, ________, _________, __________, and _________ specifications for that table.

A

data

indexes

triggers

constraints

permission

42
Q

Constraints are used to specify rules for the data in a table. You can use them to limit the type of _________ that goes into a table. This allows you to ensure the ________ and __________ of the data in the table. Any violation between the _________ and the ________ action will ________ the action.

A

data

accuracy

reliability

constraint

data

abort

43
Q

Update Hero Table: See attached image…

A

ALTER TABLE

ADD COLUMN

44
Q

Hero_Name is always given value: See attached image…

A

NOT NULL UNIQUE

FOREIGN KEY

REFERENCES

45
Q

Remove Hero Table: See attached images…

A

DROP TABLE

46
Q

Create New Hero Table: See attached images…

A

CREATE TABLE

47
Q

To achieve 1st normal form, your tables cannot have

A

Repeating Groups

48
Q

Given a table called Customer, which query will find only customers who have a last name beginning with ‘Z’?

A

SELECT * FROM CUSTOMER WHERE LAST_NAME LIKE ‘Z%’

49
Q

The _____ constraint allows a record to reference a record in another table.

A

Foreign Key

50
Q

To achieve 2nd normal form, your tables cannot have _______ or ________.

A

Repeating Groups

Partial Dependencies

51
Q

To achieve 3rd normal form, your tables cannot have ________, _________, and __________.

A

Partial Dependencies

Repeating Groups

Transitive Dependencies

52
Q

Given a table Users with fields ID (Number) and Username (varchar), which of the following will create a new record?

A

INSERT INTO USERS VALUES (1, ‘DAN’);

INSERT INTO USERS(USERNAME) VALUES (‘DAN’);

53
Q

Given a Customer table and Order table that references Customer, which of the following will find only customers who have placed an order?

A

SELECT * FROM CUSTOMER C INNER JOIN ORDERS O ON O.CUST_ID = C.CUST_ID;

54
Q

The ____ statement is designed specifically to remove rows from a table.

A

DELETE

55
Q

The constraint that defines the unique identifier for a row is ______

A

Primary Key

56
Q

Given a Customer table and Order table that references Customer, which of the following will find only customers who have never placed an order?

A

select * from orders o right join customer c on o.cust_id = c.cust_id where o.order_id is null;

OR

select * from customer c left join orders o on o.cust_id = c.cust_id where o.order_id is null;

Technical Interview Questions for Full Stack Software Engineers (13 decks)

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