Logo

Go on. Explore!

lvl 0 • 0 xp

lvl 1

HomeMarketDecksInterests
SQL

SQL

memorize.aimemorize.ai (lvl 286)
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%
(0)
(0)
(0)
23 cards
()
Section 1

Preview this deck

Database Management System

Front

1 / 23

SQL
Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (23)

Section 1

(23 cards)

Database Management System

Front

* Make it easy to manage large amounts of information *Handles Security *Backups *Import/Export Data *Interact with software applications such as a server or website

Back

WILDCARDS find all employee from table whose birthday is in jan

Front

SELECT * FROM Table WHERE dob LIKE '%-01-%'; or '____-01%'

Back

Database

Front

Any collection of related information that can be stored in many ways such as pen and paper or computer.

Back

Surrogate key vs Natural Key

Front

surrogate key has no value outisde the database like employee i.d while natural key is like ssn and exists in the real world.

Back

C.R.U.D (4 main operations performed on database)

Front

Create Read Update Delete

Back

2 Types of Database

Front

Relational -organizes info in a table with rows and columns -a key identifies each row Non-relational -graphs, docs etc

Back

row vs column

Front

individual entry vs attribute

Back

SQL

Front

Structured Query Language -Interacts with RDMS -does CRUD -Defines tables and structures -SQL code is not always the same across all RDMS

Back

how to get rid of triger

Front

write in terminal DROP TRIGGER name;

Back

Composite Key

Front

needs 2 primary or foreign key to uniquely identify an entry in the table like the amount produced from a transaction between a client and seller.

Back

if else trigger

Front

DELIMITER $$ CREATE TRIGGER my_trigger BEFORE/after INSERT/update/delelte ON employee FOR EACH ROW BEGIN IF NEW.sex = 'M' THEN INSERT INTO trigger_test VALUES('added male employee'); ELSEIF NEW.sex = 'F' THEN INSERT INTO trigger_test VALUES('added female'); ELSE INSERT INTO trigger_test VALUES('added other employee'); END IF; END$$ DELIMITER ;

Back

7 Data Type

Front

Int decimal (total num, nums after decimal) VARCHAR(string count) date timestamp('yr-month-day')

Back

Primary Key

Front

uniquely defines a record in a databse..default unique and not NUll

Back

Functions -ADD names of female from table that were born after a certain date -AVERAGE -how much female and malein table

Front

SELECT COUNT(name) FROM Table WHERE sex= 'F' AND bday> "1996-10-13"; AVG() SUM() SELECT COUNT (sex), sex FROM Table GROUP BY sex;

Back

Trigger

Front

-Cause on action to be executed if a specific event occure -executed on command line in 3 parts DELIMITER $$ CREATE TRIGGER my_trigger BEFORE INSERT ON employee FOR EACH ROW BEGIN INSERT INTO trigger_test VALUES(NEW.firstname); END$$ DELIMITER ;

Back

Select distinct first and last name as surname and forename from employee table and order by salary then name in descending order..the first 5

Front

SELECT DISTINCT firstname AS Forename, lastname AS Surename FROM Employee ORDER BY Salary, name DESC LIMIT 5;

Back

Nested Query

Front

WHERE thing IN/= ( another query);

Back

Join Function

Front

-Basically joins information from two table, but mainly used to select for overlapping info eg. all the manager info SECLECT * FROM Employee JOIN Manager ON Employee.id=Manager.id; if we use LEFT JOIN everything from the first table will be included and only the matching criteria for manager table would appear RIGHT JOIN is opposite

Back

ON DELETE CASCADE VS ON DELETE SET NULL

Front

first -used when we have a composite key..so one value cannot exist without the other -deletes all rows including them second -sets value to null in all table conting it

Back

SQL 4 parts

Front

*DATA QUERY LANGUAGE - USED TO QUERY THE DATABASE FOR INFORMATION *DATA DEFINITION LANGUAGE-USE FOR DEFININD DATA SCHEMA(LAYOUT) *DATA CONTROL LANGUAGE -CONTROL WHO HAS ACCESS TO WHAT ..USER AND PERMISSION MANAGEMENT DATA MANIPULATION- INSERT,UPDATE AND DELETE DATA FROM DATABSE

Back

UNION rule

Front

-Same amount of columns -Same Datatype -Joins Queries

Back

Databse Query

Front

A request to a Database for specific information that gets more complex the bigger the databse such as a google search.

Back

Foreign Key

Front

Points to another entry in another database or another entry in the same databse Cascade - If the rows for the parent table are removed then corresponding foreign keys columns are also removed automatically then is named as the Cascade Delete.

Back