SQL commands Flashcards
(35 cards)
What is Data Definition Language?
it includes SQL commands that create and manipulate database objects such as tables and indexes
What is Data Manipulation Language?
it includes SQL commands to insert, update read and select data within database tables
What is Data Control Language?
it includes SQL commands to control who has access to the data of the database
Syntax for creating a database
create database <database></database>
Syntax for creating a schema
create schema <schema> AUTHORIZATION <authorization></authorization></schema>
Syntax for creating a table
create table <[schema name.]table name>(column1 data type [constraints])
Syntax for creating an index
create [unique] index <index> on <table name>(column1, [column2])</index>
What are indexes
They are special lookup tables that store selected columns (attributes) and organizes them in an orderly manner such that the retrieval and access of the row instance is more efficient
Syntax for making a foreign key in a table
foreign key(<column>) references <table name>[(column name)][ ON<update/delete> <action>]</action></column>
What is on update no action?
when a primary key of a parent table is deleted and the results to any row in the dependent table without a primary key, then the update is rejected
What is on delete cascade?
if any row instance in the parent table is deleted, then it’s connected dependents are also deleted
What is on delete restrict?
if any row instance in the parent table is deleted, and its primary key exists as a foreign key in a dependent table, then the delete command will be rejected
What is on delete set NULL
if any row instance of a parent table is deleted, its foreign keys in dependent tables will be set to NULL
What is the check constraint?
it is used to limit the value of attributes by giving conditions to the data
Syntax that includes a check constraint
Area_code char(4) check (area_code in (‘6200’,’6300’))
What is the “in” operator?
It can be used in place of the “or” operator
Syntax for inserting data to table rows
insert into <table name>[(column1), (columnN)] values(value1, valueN)
Syntax for selecting data from a table row
select <column> from <table list> [where <condition>]</condition></column>
Syntax for updating table rows
update <table name> set <column1> = <expression [, columnN name = <expression>] [where <condition>]</condition></expression></column1>
Syntax for deleting table rows
delete from <table name>[ where <condition>]</condition>
Syntax for inserting data to a table row with select subquery
insert into <table name>[(<column_list>)] select <column_list> from <table name></column_list></column_list>
What is the “like” special operator?
it checks whether an attribute value matches with a given string pattern
Syntax that includes a like operator
select * from item where itemName like ‘p%’
What is the “%a” wild card?
searches for characters ending with ‘a’
