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

Foreign key

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)

Foreign key

Front

is a key to the other table primary key You can have as many as you want

Back

What are Tables and Fields?

Front

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records

Back

What is referential integrity?

Front

Relationship between the tables.

Back

List the different types of relationships in SQL.

Front

One-to-One This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table. One-to-Many & Many-to-One This is the most commonly used relationship where a record in a table is associated with multiple records in the other table Many-to-Many - This is used in cases when multiple instances on both sides are needed for defining a relationship. Self Referencing Relationships - This is used when a table needs to define a relationship with itself

Back

What is Database?

Front

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches

Back

What is View

Front

SQL object that has select or join object It is a virtual table

Back

What is inner join?

Front

You will see only matching records from both queries

Back

What is a Query?

Front

A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query

Back

SOL(structured query language) objects

Front

Database Table Store procedure View Trigger

Back

What are some common clauses used with SELECT query in SQL?

Front

Some common SQL clauses used in conjuction with a SELECT query are as follows: WHERE clause in SQL is used to filter records that are necessary, based on specific conditions ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC) GROUP BY clause in SQL is used to group records with identical data and can be used in conjuction with some aggregation functions to produce summarized results from the database HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since WHERE clause cannot filter aggregated records

Back

What are UNION, MINUS and INTERSECT commands?

Front

The UNION operator combines and returns the result-set retrieved by two or more SELECT statements. The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first. The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets. Certain conditions need to be met before executing either of the above statements in SQL: Each SELECT statement within the clause must have the same number of columns The columns must also have similar data types The columns in each SELECT statement should necessarily have the same order

Back

Two type of indexes

Front

a. Clustered b. Non-Clustered

Back

What are Constraints in SQL?

Front

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are NOT NULL - Restricts NULL value from being inserted into a column. CHECK - Verifies that all values in a field satisfy a condition. DEFAULT - Automatically assigns a default value if no value has been specified for the field. UNIQUE - Ensures unique values to be inserted into the field INDEX-Indexes a field providing faster retrieval of records PRIMARY KEY - Uniquely identifies each record in a table FOREIGN KEY - Ensures referential integrity for a record in another table

Back

What is the SELECT statement?

Front

SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set

Back

What are the TRUNCATE, DELETE and DROP statements?

Front

DELETE statement is used to delete rows from a table. TRUNCATE command is used to delete all the rows from the table and free the space containing the table. DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database.

Back

Clustered index

Front

Special type of index that physically sorts data. Its stored inside the table. Can be only one per table

Back

What is a left join

Front

Is going to return every record from the left table whatever matches to the right table. If it does not match it will show NULL

Back

What is Data Integrity?

Front

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database

Back

Difference between delete and truncate

Front

"delete "- deletes raw by raw and has where clause, truncate clears entire table. All identities and indexes will continue with delete, with truncate will starts from initial value. Delete has LOG file, truncate does not.

Back

What is a primary key?

Front

Unique column in the table. Can be only one in a table. Can consist of multiple columns(compound/composite).

Back

Difference between store procedure and function

Front

function function will always return 1 value back

Back

What is a View?

Front

A view in SQL is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Back

Type of Function

Front

Aggregate function, UDF- user define function, System function

Back

Stored Procedure

Front

its a SQL object that has queries inside. Its good for security and re-usability. 2 types: uspGet only for select uspSet- for insert, update, delete

Back

What is Cursor? How to use a Cursor?

Front

A database cursor is a control structure that allows for traversal of records in a database. Cursors, in addition, facilitates processing after traversal, such as retrieval, addition and deletion of database records They can be viewed as a pointer to one row in a set of rows. Working with SQL Cursor 1. DECLARE a cursor after any variable declaration. The cursor declaration must always be associated with a SELECT Statement. 2. Open cursor to initialize the result set. The OPEN statement must be called before fetching rows from the result set 3. FETCH statement to retrieve and move to the next row in the result set 4. Call the CLOSE statement to deactivate the cursor 5. Finally use the DEALLOCATE statement to delete the cursor definition and release the associated resources

Back

Full Join

Front

Right and Left join together. You will see every record from the left whatever matches to the right. If it does not match you will see NULL

Back

Rollback and commit

Front

you have to begin transaction, for example if I need to update multiply tables and every update works, I will commit my changes (finalize the changes), but if something does not work I will rollback the changes (undo). You can not do rollback after commit. Will use in try catch block.

Back

Trigger

Front

SQL object that's created on a table. You can have one table for insert, update and delete or you can have 3 different ones

Back

How many system databases?

Front

After you installed SQL you have 4 databases.

Back

What is constraint?

Front

It's a rule. You can not insert the value in a foreign key that does not exist in the other table primary key to which it points

Back

What is Normalization?

Front

Normalization represents the way of organizing structured data in the database efficiently. It includes creation of tables, establishing relationships between them, and defining rules for those relationships Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database

Back

What is a Self-Join?

Front

A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query

Back

Differences between WHERE and HAVING

Front

WHERE sorts row by row and does not work with aggregate functions HAVING works with GROUP BY and aggregate functions

Back

Primary key vs clustered index

Front

Primary: Doesn't sort data One per table Only unique Clustered: Sorts physically One per table Not necessarily unique

Back

What is a Cross-Join?

Front

Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN

Back

What is an Alias in SQL?

Front

An alias is a feature of SQL that is supported by most, if not all, RDBMSS. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields A table alias is also called a correlation name An alias is represented explicitly by the AS keyword but in some cases the same can be performed without it as well. Nevertheless, using the AS keyword is always a good practice

Back

What is the difference between DROP and TRUNCATE statements?

Front

If a table is dropped, all things associated with the tables are dropped as well This includes the relationships defined on the table with other tables, the integrity checks and constraints, access privileges and other grants that the table has To create and use the table again in its onginal forrm, all these relations che constraints, privileges and relationships need to be redefined Howeyer, if a table is truncated none of the above problems exist and the table retains its original structure.

Back

What type of joins do you know?

Front

a. Inner join b. Left outer join c. Right outer join d. Cross e. Full

Back

How do you call a table without a Primary Key?

Front

Heap

Back

What is a Subquery? What are its types?

Front

A subquery is a query within another query, also known as nested query or inner query It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively For example, here we fetch the contact information for students who have enrolled for the maths subject

Back