Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

40 cards

()

Section 1

Preview this deck

Normalization

Front

1 / 40

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (40)

Section 1

(40 cards)

Normalization

Front

the process of minimizing redundancy and dependency by organizing fields and table of a database.

Back

One to One Relationship

Front

each record in Table A can have only one matching record in Table B

Back

What is the difference between Cluster and Non-Cluster Index?

Front

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index. A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

Back

String data types

Front

char, varchar, varchar(max), nchar, text, nvarchar, nvarchar(max), ntext, binary, varbinary, varbinary(max), image

Back

DB trigger

Front

a code or programs that automatically execute with response to some event on a table or view in a database. Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.

Back

Database Query

Front

a code written in order to get the information back from the database.

Back

how many forms of normaliztion?

Front

Five (1NF, 2NF..)

Back

many-to-many relationship

Front

In databases, a relationship in which one record in Table A can relate to many matching records in Table B, and vice versa.

Back

What is SQL NOT NULL constraint?

Front

used to ensure that the value in the filed cannot be a NULL

Back

One to Many Relationship

Front

a relationship between two tables where one record in the first table corresponds to many records in the second table

Back

Auto increment or Identity keyword

Front

allows the user to create a unique number to be generated when a new record is inserted into the table. One is used in Oracle and latter is used in SQL server

Back

How many clustered index / nonclustered index can a table have?

Front

1 / 999

Back

DeNormalization

Front

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed.

Back

non-correlated subquery

Front

can be considered as independent query and the output of subquery are substituted in the main query.

Back

What is the difference between DELETE and TRUNCATE commands?

Front

DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement. TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

Back

Database Relationship

Front

a link between the primary key of one table and the foreign key of another table

Back

Clustered indexes

Front

reorders the physical order of the table and store the data rows in the table based on their key values

Back

Inner Join

Front

return rows when there is at least one match of rows between the tables

Back

What is a Database Cursor?

Front

a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

Back

subquery

Front

a query within another query.

Back

Difference between left and right outer join?

Front

result of LEFT outer join is INNER JOIN + unmatched rows from LEFT table and RIGHT OUTER join is INNER JOIN + unmatched rows from the right-hand side table.

Back

View

Front

a virtual table which consists of a subset of data contained in a table. this virtual table are not virtually present, and it takes less space to store. used to simply retrieve the results of complicated queries that need to be executed often. used to restrict access to the database or to hide data complexity. can have data of one or more tables combined

Back

correlated subquery

Front

A subquery that executes once for each row in the outer query.

Back

Unique key constraint

Front

Uniquely identified each record in the database. This provides uniqueness for the column or set of columns. There can be many defined per table.

Back

SQL Constraints

Front

NOT NULL. CHECK. DEFAULT. UNIQUE. PRIMARY KEY. FOREIGN KEY.

Back

Many to One Relationship

Front

One type of data relationship in which many records in a table are related to one record in another table

Back

Stored Procedure

Front

is a collection of SQL statements that have been created and stored in the database to perform a particular task.

Back

What is a DEFAULT constraint?

Front

used to include a default value in a column when no value is supplied at the time of inserting a record.

Back

Full Join

Front

return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

Back

Right join

Front

return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

Back

FOREIGN KEY

Front

a key used to link two tables together. This key in a table is linked with the PRIMARY KEY of another table. Relationship needs to be created between two tables by referencing this key with the primary key of another table. A table can have many of this key.

Back

temp table

Front

a temporary storage structure to store the data temporarily

Back

Index

Front

used to speed up the performance of queries. creates an entry for each value and it will be faster to retrieve data. can be created on one column or a group of columns.

Back

Query and SubQuery

Front

The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.

Back

JOIN

Front

This is a keyword used to query data from more tables based on the relationship between the fields of the tables.

Back

Number data types

Front

bit (0,1, NULL), int (0-255), smallint (-32,768-32,767), tinyint, bigint, decimal, smallmoney, numeric, money, float, real

Back

Left Join

Front

return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Back

nonclustered index

Front

does not alter the physical order of the table and maintains logical order of data.

Back

primary key

Front

A combination of fields which uniquely specify a row. it has implicit NOT NULL constraint, meaning values cannot be NULL. There can only be ONE defined per table,

Back

What is a CHECK constraint?

Front

used to limit the value that is accepted by one or more columns. E.g. 'Age' field should contain only the value greater than 18. CREATE TABLE EMP_DETAILS(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Age INT CHECK (AGE > 18), PRIMARY KEY (EmpID));

Back