Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

DataBase PostgreSQL

memorize.ai (lvl 286)

(0)

25 cards

()

Section 1

Preview this deck

Some SQL Statement

Front

1 / 25

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (25)

Section 1

(25 cards)

Some SQL Statement

Front

ALTER TABLE child_table ADD CONSTRAINT constraint_name FOREIGN KEY (c1) REFERENCES parent_table (p1); UPDATE table_name SET column1 = value1, column2 = value2 WHERE [condition]; INSERT INTO TABLE_NAME (column1 , column2) VALUES (value1, value2);

Back

Types of joins in SQL

Front

1-Inner Join 2-Left outer Join 3-Right outer join 4-full outer join 5-exclusive outer join: every thing except the common 6-self join: Table join with self 7-cross join: select* from a cross join b every single index with all other indexes

Back

Normalization : 1NF

Front

First Normal Form: 1-columns are small and reasonably possible, avoid redundancy 2-each table has a primary key

Back

Multiplicity

Front

type of relationship between tables 1) 1-to-1 ex: SSN to Person 2) 1-to-many ex: company to employees 3) many-to-1 ex: teeth to person 4) many-to-many: ex: parents to children create separate table to hold relationship

Back

DML

Front

Select, Insert, Update, and Delete

Back

SQL Sub-Languages

Front

1-DDL: Data Definition Language 2-DML: Data Manipulating language 3-TCL: Transaction Control Language 4-DCL: Data Control Language

Back

Normalization: 2NF

Front

1-Apply the 1NF rules 2-Remove Partial Dependency 3-Create the primary/foreign relationship between these tables

Back

Aggregate function

Front

Take in multiple row s and return single value ex: count-AVG-MAX-Min

Back

DDL

Front

CREATE, ALTER, DROP, TRUNCATE

Back

Scale Function

Front

Take one value and return one value ex: CurrentTime-Length

Back

Joins

Front

Brings data from two tables together into one query.

Back

Constraint

Front

1-Primary key: it should be unique in table, key of the row, it could be serialalized 2-Foreign key: reference value to another table, relation between tables 3-Not Null 4-Unique 5-Check: user defined constraint 6-Exclusion: not all values are same

Back

What is SQL

Front

Structured Query Language, how to communicate with database

Back

Transaction example

Front

Create procedure update Address as begin begin try Begin Transaction Query1...... Query2...... commit transaction end try begin catch rollback transaction end catch end

Back

RDBMS

Front

Relational Database Management System

Back

Stored Procedures

Front

Stored Procedures: 1-Donn't return value 2-can do DML 3-can call a function 4-can call other Stored Procedures

Back

GROUPBY

Front

we use it with aggregate function example: select city, sum(salary) as [salaryT] from X group by city having city='USA' having we use it after group by, used just with select

Back

SQL Index

Front

CREATE INDEX index_name ON table_name (column1, column2, ...);

Back

Normalization: 3NF

Front

1-must be in 2NF 2-Transitive Dependencies 3-All columns must be directly related to the primary key in another table

Back

Types of Functions

Front

1-Aggregate function 2-Scale Function

Back

TCL

Front

Commit, Rollback, save point

Back

Transaction

Front

A: Atomic: smallest unit of work done-commit all or nothing C: Consistency: should do the same thing every time I: Isolation: should not effect or be effected by other transaction D: Durability: If there is a system error it should RollBack

Back

Function VS Stored Procedures

Front

Function: 1-return single value 2-can select Query 3-can call other functions 4-cannot call store procedures

Back

Delete Vs Truncate

Front

Delete: DML delete some or all data, you can RollBack, you can delete trigger, slow. Truncate: DDL, delete all table, cant rollback, no trigger, faster.

Back

DCL

Front

Grant, Revoke

Back