Structured query language sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 9

Structured Query Language - SQL PowerPoint PPT Presentation


  • 37 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

Structured Query Language - SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Structured query language sql

Structured Query Language - SQL

Carol Wolf

Computer Science


Structured query language sql 2577454

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

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 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

Update Queries - Insert

  • "Insert into courses Values ('CS122', 'Programming II', 4) "

    • The string fields must be surrounded by quotes.

    • The numeric field does not have them.

  • "Insert Into courses Values ('" + number + "', '" + name + "', " + credits) "

    • 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

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

  • "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

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) "

    • This adds a new table with four columns.

  • "Alter Table courses Add professor varchar(20)"

    • 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

Datatypes for database queries


  • Login