SQL Practice

HAVING clause vs WHERE

Can only be used with a select statement and is used in a group by clause WHERE - applied to each row before before they are part of the group by function

Trigger

special type of stored procedure defined to execute automatically BEFORE(INSERT, DELETE, Update) AFTER

recursive stored procedure

stored procedure that calls by itself until it hits some boundary condition

Enitites

things in the real world that can be stored in a db

set operators

union, intersect, minus- keeps rows from left that are not included in the right

Index

used for the fast retrieval of records from the table

view

virtual table which contains a subset of data in a table Used for -making complex queries simple -ensuring data independence -restricting access to data -different views of same dat

different authentication modes

windows mode and mixed mode

operator used for pattern matching

LIKE

Case-manipulation functions

LOWER, UPPER, INITCAP- first letter in uppercase

aggregate function

Performs a calculation on a set of values and then returns a single value.

relationships

Relations or links between entities that have something to do with each other

DROP

Removes a table and cannot be rolled back

right join

Return all rows from the right table, and the matched rows from the left table

full join

Return all rows when there is a match in ONE of the tables

left join

Returns all records from the left table, and the matched records from the right table

Inner Join

Returns records that have matching values in both tables

difference between char and varchar2 data type

char is used for fixed length var char is variable and can use any length

levels of constraints

column level table level

Constraints

constraints are used to limit the data type NOT NULL- null cannot be stored UNIQUE - all values in column need to be different CHECK - ensure all meet a specified condition DEFAULT- Sets default values when none are specified INDEX- create and retrieve data from a database

unique index

does not allow the field to have duplicate values

Create invitations to a party by retrieving first name, last name, and email FROM Customers ORDER BY last name

first name, last name, and email FROM Customers ORDER BY last name

stored procedure

function consisting of multiple SQL statements to access the database -avoid writing code over and over -disadvantage is that it uses more data

clause

helps limit the result by setting a condition to the query

Different types of dbms

hierarchical, relational, network, and object orientated database

local vs global variable

local -can only be used or exist within the function global -can be accessed throughout the program

foreign key

maintains referential integrity by enforcing a link between tables

Types of user defined functions

multi-statement valued, scalar, and Inline table valued

Is null the same as 0 or blank space

no null means the value is unobtainable, unknown, or unassigned. not a number or charachter

field

number of columns in a table(columns)

Relationships(links between entities)

one to one, many to one, many to many, self referencing

mySQL

open source database relational system

Normalization

process of organizing data to avoid duplication and redundancy -better organization -efficiency of data access - more tabled with smaller rows 1normalformal-PK, 2nf(new table), 3nf(no transitive functional dependencies)

data warehouse

A central data repository containing information drawn from multiple sources that can be used for analysis, intelligence gathering, and strategic planning.

table

A collection data in an organized manner such as rows and columns

Alias command

A command used to create special variables that are shortcuts to longer command strings. a.people

Denormalization

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. Denormalization potentially yields data anomalies.

subquery

A query inside another query. subqueries are executed first then result is used for the main query

Scalar Function

A user defined function that returns a single value.

Group functions

AVG, MIN, MAX, COUNT, SUM, VARIANCE

ACID

Atomicity Consistency Isolation Durability

auto increment

Auto-increment allows a unique number to be generated when a new record is inserted into a table. Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. *IDENITY KEY WORD CREATE TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )

BETWEEN VS IN

BETWEEN- used to display rows between specific values IN- check for values in a specific set of values

clustered index vs non clustered index(easy retrieval of data)

Clustered index- alters the way records are stored as it sorts out rows by which column is selected can only be one Non clustered- Does not alter the way its stores but instead creates a separate object within that points back to the original table rows

Subsets of SQL

DDL -Commands to define database schema DML -Commands to deal with manipulation of data DCL -Commands for the rights and permissions of the db TCL -Commands to deal with the transactions of the db

DCL

Data Control Language

DDL

Data Definition Language

DML

Data Manipulation Language

What is the difference between DELETE and TRUNCATE

Delete -Remove a row from the table -Slower than truncate -You can roll back data Truncate -Used to delete all rows from a table -You cannot roll back data -faster

Select all records from the Customers table, sort the result alphabetically by the column City.

SELECT * FROM Customers ORDER BY City

Select all records from the Customers table, sort the result reversed alphabetically by the column City.

SELECT * FROM Customers ORDER BY City DESC

Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City.

SELECT * FROM Customers ORDER BY Country, City

Select all records where the first letter of the City is an "a" or a "c" or an "s".

SELECT * FROM Customers WHERE City LIKE '[acs]%';

Use the NOT keyword to select all records where City is NOT "Berlin".

SELECT * FROM Customers = ''; WHERE NOT City = 'Berlin';

Data Integrity

accuracy of data, consistency of data,

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' and 'Petterson'

With SQL, how can you return the number of records in the "Persons" table?

SELECT COUNT(*) FROM Persons

count of records

SELECT COUNT(*) FROM table; SELECT * FROM TABLE

Select all the different values from the Country column in the Customers table.

SELECT DISTINT FROM

SQL Query to get the date

SELECT GETDATE();

How can you fetch first 5 characters of the string?

SELECT RIGHT(Studentname, 5)

REPLACE vs STUFF function

STUFF -Overwrite an existing character or sting REPLACE -Overwrite existing characters in all occurances

How to select unique records from a table?

Select DISTINCT StudentID, StudentName from Student.

DBMS

Software used to create, enter, edit, and retrieve data in a database.

how to insert null values when inserting data

Specifying the null keyword in the values clause

merge statement

allows conditional update or insertion of data UPDATE if row exists INSERT if not

TCL

Transaction Control Language

what is a unique key?

Uniquely identifies a single row in the table. Multiple values allowed per table. Null values allowed.

How can you fetch common records from two tables?

You can fetch common records from two tables using INTERSECT.

collation

a set of rules that determine how data is sorted and compared