Transcript Structured Query Language

Structured Query Language - SQL
Carol Wolf
Computer Science
SQL


Used for relational databases, which consist of rows and
columns.
SQL is not case sensitive. Queries are usually in all upper
case.




These slides will use upper case only for the first letter.
In Rails, you can use find_by_sql(…).
The query inside the parentheses is in quotation marks.
You have to debug the queries yourself.
The Select Query

"Select * From courses "


This returns all the rows in the courses table.
"Select * From courses Where Name Like 'A%'"
This will narrow the search down to courses with names
beginning with A.
"Select * From courses Where Name Between 'A%' And 'C%'"
 This returns all the courses with names beginning with A, B and
C.



If the name occurs several times in the table, all those
rows will be returned. To restrict this to the first such
row, you add .first to the query.
Select and variables

"Select * From courses Where Name = '" + name + "'"




Variables that are strings must be enclosed in quotation marks.
This usually requires quotes (single) within quotes (double).
This is a major drawback to SQL.
"Select * From courses Where credits >= " + amount


Variables that are numeric do not take quotation marks.
These are definitely easier to manage.
Update Queries - Insert

"Insert into courses Values ('CS122', 'Programming II', 4) "



"Insert Into courses Values ('" + number + "', '" + name + "', " + credits) "





The string fields must be surrounded by quotes.
The numeric field does not have them.
In order to use string variables, you have to have quotes
around the variables.
These are added in using the plus sign for concatenation.
"', '" – This shows a double quote followed by a single quote
followed by a comma, etc.
Inserting more than two or three string variables is hard on
the eyes.
If insert fails, it returns 0, otherwise it returns the number
of rows inserted.
Update Query - Delete

"Delete From courses Where id = '" + key_id + "'“


This deletes the row in the table with the key id.
Here the key id is a variable.
Update

"Update course Set credits = 4 Where id = 5"


This changes the credits for the course with id 5.
"Update courses Set credits = " + new_credits + " Where id = " + id + "
This changes credits using variables.
"Update course Set number = 'CS122', credits = 4 Where id = 5"
 This changes two fields at the same time.



"Update course Set number = " + new_number + ",
credits = " + new_credits +
" Where id = + id + “
 Change two fields using variables.
Create and Alter




These change the database itself.
Create adds a new table to the database.
Alter adds or drops a column.
"Create Table professors (id integer,
name varchar,
email varchar,
department varchar) "


"Alter Table courses Add professor varchar(20)"


This adds a new table with four columns.
This adds a new column to an existing table.
"Alter Table courses Drop Column credits"

This removes the credits column from the table.
Datatypes for database queries
integer(size)
int(size)
smallint(size)
Holds integers only. The maximum number of digits is
specified in parenthesis.
decimal(size,d)
numeric(size,d)
Holds numbers with fractions. The maximum number of
digits is specified in "size". The maximum number of digits to
the right of the decimal is specified in "d".
float(n)
real
double
Floating point number with n binary digits of precisions.
32-bit floating point number.
64-bit floating point number.
char(size)
Holds a fixed length string (can contain letters, numbers, and
special characters). The fixed size is specified in parenthesis.
varchar(size)
Holds a variable length string (can contain letters, numbers,
and special characters). The maximum size is specified in
parenthesis.
date(yyyymmdd)
Holds a date.