DataBases & SQL

DML commands* insert, update, select, delete

insert into salesreps values('al', 19, 65000, 1, false); select * from salesreps; select name from salesreps; update salesreps set salary = salary - 20000; update salesreps set salary = salary + 5000 where name = 'al'; insert into salesreps values('benny', 32, 65000, 3, true); delete from salesreps where experienced = 0;//deletes anyone with no experience

The Structured Query Language (SQL)

is the set of instructions used to interact with a relational database. In fact, SQL is the only language that most databases actually understand. Whenever you interact with such a database, the software translates your commands (whether they are mouse clicks or form entries) into SQL statement that the database knows how to interpret. SQL has three major components: the Data Manipulation Language (DML), the Data Definition Language (DDL), and the Data Control Language (DCL).

The Data Control Language (DCL)

is used to manage user access to databases. It consists of two commands: the GRANT command, used to add database permissions for a user, and the REVOKE command, used to take away existing permissions. These two commands form the core of the relational database security model.

the JOIN statement.

these statements allow you to combine data in multiple tables to quickly and efficiently process large quantities of data. *select salesreps.name, salesreps.experienced, discipline.incident, discipline.comments from salesreps, discipline WHERE discipline.name;

No

what.

Queries (complex requests)

Access queries provide the capability to combine data from multiple tables and place specific conditions on the data retrieved.

Primary Keys

All tables should have "primary key". Column that is 100% unique, no rows can have the same primary key. * SSN, RedID,

Databases

Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables. Database tables consist of columns and rows. Each column contains a different type of attribute and each row corresponds to a single record. *"FirstName", "LastName" and "TelephoneNumber." - then rows with particular information

Structure of an SQL Command

SQL commands are designed to have a syntax very similar to the English language. They normally begin with a command statement describing the action you wish to take, then a clause that describes the target of the command (such as the specific table within a database affected by the command) and then a series of clauses that provide additional instructions. Often, simply reading an SQL statement will tell you what's going on. * DELETE FROM students WHERE graduation_year = 2011

The Data Definition Language (DDL)

contains commands that are less frequently used. DDL commands modify the actual structure of a database, rather than the database's contents. Examples of commonly used DDL commands include: use certain database ( those used to generate a new database table (CREATE TABLE), modify the structure of a database table (ALTER TABLE), and delete a database table (DROP TABLE). * CREATE DATABASE employees (creates an empty DB "employees)

The Data Manipulation Language (DML)

contains the subset of SQL commands used most frequently - those that simply manipulate the contents of a database in some form. The four most common DML commands are used to retrieve information from a database (the SELECT) command, add new information to a database (the INSERT command), modify information currently stored in a database (the UPDATE command) and remove information from a database (the DELETE command).

example using DDL commands: create, alter, drop

create database Employees; use employees; create table salesreps(name varcahar(200), age int not null); alter table salesreps add salary int null; alter table sales reps(salestoday int null, experienced boolean not null); drop table sales reps; drop database employees;