SQL
First normal form (1NF)
-Define the data items required, because they become the columns in a table. -Place the related data items in a table. -Ensure that there are no repeating groups of data. -Ensure that there is a primary key.
List 2 reasons for Database Normalization:
1-Eliminating redundant data, for example, storing the same data in more than one table (thereby reducing the amount of space a database consumes). 2-Ensuring data dependencies make sense( thereby ensuring that data is logically stored..)
field
A data structure for a single piece of data. ____s are organized into records, which contain all the information within the table .
table
A database object which is a collection of related data entries consisting of numerous columns and rows.
What is a NULL value?
A value in a field that appears to be blank, which means a field with a ____ value is a field with no value.It is very important to understand that a ____ value is different than a zero value or a field that contains spaces. A field with a _____ value is the one that has been left blank during a record creation.
column
A vertical entity in a table that contains all information associated with a specific field in a table. Usually, a ___ in a table contains the values of a single field. However, you can show several fields in a ______ by using a Formula or a Combination field. A _____ just one way to display the contents of a field.
SQL DROP INDEX Statement
ALTER TABLE table_name DROP INDEX index_name;
SQL ALTER TABLE Statement (Rename)
ALTER TABLE table_name RENAME TO new_table_name;
SQL ALTER TABLE Statement
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
Applications of SQL
Allows users to access data in the relational database management systems. Allows users to describe the data. Allows users to define the data in a database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre-compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views.
SQL COMMIT Statement
COMMIT;
SQL CREATE DATABASE Statement
CREATE DATABASE database_name;
SQL CREATE TABLE Statement
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
SQL CREATE INDEX Statement
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);
SQL Commands
CREATE, SELECT, INSERT, UPDATE, DELETE and DROP.
Third Rule of First Normal (1NF)
Create a primary key for each table which we have already created.
CREATE
Creates a new table, a view of a table, or other object in the database.
INSERT
Creates a record.
SQL DELETE Statement
DELETE FROM table_name WHERE {CONDITION};
SQL DESC Statement
DESC table_name;
SQL DROP DATABASE Statement
DROP DATABASE database_name;
SQL DROP TABLE Statement
DROP TABLE table_name;
First Rule of First Normal (1NF)
Define the data items and put the related columns into their own table.
DROP
Deletes an entire table, a view of a table or other objects in the database.
DELETE
Deletes records.
row
Each individual entry that exists in a table (also called a record)
User-Defined Integrity
Enforces some specific business rules that do not fall into entity, domain or referential integrity.
Domain Integrity
Enforces valid entries for a given column by restricting the type, the format, or the range of values.
Second Rule of First Normal (1NF)
Ensure that there are no repeating groups of data.
NOT NULL Constraint
Ensures that a column cannot have a NULL value.
UNIQUE Constraint
Ensures that all the values in a column are different.
List the categories of data integrity that exist with each RDBMS.
Entity Integrity Domain Integrity Referential integrity User-Defined Integrity
SQL is case sensitive. True or False?
False. SQL is case insensitive, which means SELECT and select have same meaning in SQL statements.
List the first 3 Database normalization guidelines:
First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF)
GRANT
Gives a privilege to user.
SQL INSERT INTO Statement
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);
ntext (Unicode Character String Data Type)
Maximum length of 1,073,741,823 characters. ( Variable length Unicode )
image(Binary Data Type)
Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data)
varbinary(max)(Binary Data Type)
Maximum length of 2E + 31 bytes (SQL Server 2005 only). ( Variable length Binary data)
nvarchar(max) (Unicode Character String Data Type)
Maximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode)
varchar(max) (Character String Data Type)
Maximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only).
nchar (Unicode Character String Data Type)
Maximum length of 4,000 characters.( Fixed length Unicode)
nvarchar (Unicode Character String Data Type)
Maximum length of 4,000 characters.(Variable length Unicode)
binary(Binary Data Type)
Maximum length of 8,000 bytes(Fixed-length binary data )
varbinary (Binary Data Type)
Maximum length of 8,000 bytes.(Variable length binary data)
char (Character String Data Type)
Maximum length of 8,000 characters.( Fixed length non-Unicode characters)
varchar (Character String Data Type)
Maximum of 8,000 characters.(Variable-length non-Unicode data).
ALTER
Modifies an existing database object, such as a table.
UPDATE
Modifies records.
List the Relational Database Management Systems (RDMS)
MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server
DEFAULT Constraint
Provides a default value for a column when none is specified.
What is RDBMS?
RDBMS stands for Relational Database Management System.A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.
SQL ROLLBACK Statement
ROLLBACK;
SELECT
Retrieves certain records from one or more tables.
Referential integrity
Rows cannot be deleted, which are used by other records.
SQL COUNT Clause
SELECT COUNT(column_name) FROM table_name WHERE CONDITION;
SQL DISTINCT Clause
SELECT DISTINCT column1, column2....columnN FROM table_name;
SQL HAVING Clause
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);
SQL GROUP BY Clause
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;
SQL ORDER BY Clause
SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};
SQL AND/OR Clause
SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQL WHERE Clause
SELECT column1, column2....columnN FROM table_name WHERE CONDITION;
SQL BETWEEN Clause
SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;
SQL IN Clause
SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N);
SQL LIKE Clause
SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN };
SQL SELECT Statement
SELECT column1, column2....columnN FROM table_name;
xml(Misc Data Type)
Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
timestamp(Misc Data Type)
Stores a database-wide unique number that gets updated every time a row gets updated
date(Date and Time Data Type)
Stores a date like June 30, 1991
uniqueidentifier(Misc Data Type)
Stores a globally unique identifier (GUID)
time(Date and Time Data Type)
Stores a time of day like 12:30 P.M.
sql_variant(Misc Data Type)
Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
SQL
Structured Query Language: a database computer language designed for the retrieval and management of data in a relational database
SQL TRUNCATE TABLE Statement
TRUNCATE TABLE table_name;
REVOKE
Takes back privileges granted from user.
CHECK Constraint
The CHECK constraint ensures that all values in a column satisfy certain conditions.
Database Normalization
The process of efficiently organizing data in a database
Constraints
The rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.______ can either be column level or table level. Column level ______s are applied only to one column whereas, table level _____s are applied to the entire table.
Entity Integrity
There are no duplicate rows in a table.
SQL UPDATE Statement
UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ];
SQL USE Statement
USE database_name;
FOREIGN Key
Uniquely identifies a row/record in any another database table.
PRIMARY Key
Uniquely identifies each row/record in a database table.
INDEX
Used to create and retrieve data from the database very quickly.
text (Character String Data Type)
Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.
float (Approximate Numeric Data Type)
from -1.79E + 308 to 1.79E + 308
decimal(Exact Numeric Data Type)
from -10^38 +1 to 10^38 -1
numeric(Exact Numeric Data Type)
from -10^38 +1 to 10^38 -1
int (Exact Numeric Data Type)
from -2,147,483,648 to 2,147,483,647
smallmoney(Exact Numeric Data Type)
from -214,748.3648 to +214,748.3647
real(Approximate Numeric Data Type)
from -3.40E + 38 to 3.40E + 38
smallint (Exact Numeric Data Type)
from -32,768 to 32,767
bigint (Exact Numeric Data Type)
from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
money(Exact Numeric Data Type)
from -922,337,203,685,477.5808 to +922,337,203,685,477.5807
bit(Exact Numeric Data Type)
from 0 to 1
tinyint (Exact Numeric Data Type)
from 0 to 255
datetime(Date and Time Data Type)
from Jan 1, 1753 to Dec 31, 9999 (Here, datetime has 3.33 milliseconds accuracy)
smalldatetime(Date and Time Data Type)
from Jan 1, 1900 to Jun 6, 2079(smalldatetime has 1 minute accuracy.)
