{"id":83,"date":"2014-02-03T16:53:46","date_gmt":"2014-02-03T23:53:46","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=83"},"modified":"2016-10-19T15:36:54","modified_gmt":"2016-10-19T22:36:54","slug":"sql","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/databases\/sql\/","title":{"rendered":"SQL Basics"},"content":{"rendered":"

The Structured Query Language<\/span>, SQL, is a standard language to manipulate databases. There are four basic operations of databases: Create, Read, Update and Delete (CRUD). The required SQL statement for each operation depends on the data object(s) handled. Here is a summary of the most common ones:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Operation<\/td>\nSQL Statement<\/td>\nDescription<\/td>\n<\/tr>\n
Create<\/td>\nCREATE TABLE\u2026<\/td>\nCreates a new table, defining field names and data types<\/td>\n<\/tr>\n
<\/td>\nSELECT\u2026 INTO\u2026<\/td>\nCreates a new table from a query result<\/td>\n<\/tr>\n
<\/td>\nINSERT INTO\u2026 VALUES\u2026<\/td>\nCreates tuples in a table<\/td>\n<\/tr>\n
<\/td>\nINSERT INTO\u2026 SELECT\u2026<\/td>\nCreates tuples in a table obtained from a query<\/td>\n<\/tr>\n
Read<\/td>\nSELECT\u2026 FROM\u2026 WHERE\u2026<\/td>\nSelects columns from table where condition is met<\/td>\n<\/tr>\n
Update<\/td>\nUPDATE\u2026 SET\u2026 WHERE\u2026<\/td>\nUpdates field values of tuples satisfying a condition<\/td>\n<\/tr>\n
Delete<\/td>\nDELETE\u2026 FROM\u2026 WHERE<\/td>\nDeletes tuples satisfying a condition<\/td>\n<\/tr>\n
<\/td>\nDROP TABLE\u2026<\/td>\nDeletes table<\/td>\n<\/tr>\n
<\/td>\nTRUNCATE TABLE\u2026<\/td>\nDeletes all data in the table<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This Wikipedia article<\/a> in provides more details about SQL.<\/p>\n

Formatting in SQL<\/strong><\/p>\n

Before continuing with statements, here are some SQL formatting rules:<\/p>\n

1. SQL is NOT case sensitive: SELECT<\/em> is the same as select<\/em>.<\/p>\n

Note: for this reason many people use underscore to name columns instead of camel case. For instance Student_ID instead of StudentID.<\/p>\n

2. SQL ignores white space and newline characters. This gives you flexibility about formatting query statements: you can put SELECT, FROM, WHERE clauses on separate lines and indent each one, or whatever makes your code more human-readable, without affecting its machine-readability.<\/p>\n

3. Semicolons after SQL statements are the standard way to separate multiple statements. Some DBMS don’t require them for single statements.<\/p>\n

Creating Tables<\/strong><\/p>\n

In Access, when using the create table command, you need to define each field’s data type, and you should also specify the primary key. More details are here,<\/a> including how to use the menu options to create a table. You can also create a query and type the following in “SQL View”:<\/p>\n

CREATE TABLE<\/span> Students\u000b (StudentID number, StudentName text, Program text, Year number, Age number, primary key (StudentID))<\/p>\n

Data Types in SQL:<\/strong> note that these may be slightly different in different implementations of SQL: here <\/a>is a list of the types in different versions, and here<\/a> is a list of data types in Access specifically. For example, the type “text” in Access is a character string (may also contain numbers) that has a maximum of 255 characters, while the type “text” in MySQL has a maximum length of 65,535\u00a0characters, and in SQL Server the type “text” can contain up to 2 GB of data.<\/p>\n

NULL<\/span> values are allowed in all the types. Null represents an unknown value or that a value does not exist. To prohibit null values in a column you need to add the statement NOT NULL in the field declaration (e.g. StudentID Number NOT NULL).<\/p>\n

Reading: <\/strong>A typical SQL query has the form:<\/p>\n

SELECT<\/span> Columns
\nFROM<\/span> Tables
\nWHERE <\/span>Conditions<\/p>\n

The result of a SQL query is a new table called the result set<\/span>.<\/p>\n

SELECT INTO<\/span>: creates a new table from a query result set. The fields will have the same data type of the original fields.<\/p>\n

SELECT<\/span> StudentID, StudentName INTO<\/span> MM13
\nFROM Students
\nWHERE Program = \u201cMMOR\u201d AND Year = 2013 ;<\/p>\n

Creating Tuples<\/strong><\/p>\n

You can insert a tuple into a table setting the value of each field:<\/p>\n

INSERT INTO<\/span> Students \u000bVALUES<\/span> (536880, “Harry Potter”, “MMOR”, 2017, 23)<\/p>\n

Or you can create a tuple but\u00a0define only a subset of fields:<\/p>\n

INSERT INTO<\/span> Students (StudentID, StudentName<\/span>) VALUES<\/span> (536881, “Ron Weasley”)<\/p>\n

Alternatively, you can insert the rows from another table:<\/p>\n

INSERT INTO<\/span> Students SELECT<\/span> * FROM Admitted_Students<\/p>\n

Updating<\/strong><\/p>\n

You can update field values of tuples satisfying a condition. E.g. increase the age of all students by 1:<\/p>\n

\u000b UPDATE<\/span> Students\u000b SET<\/span> Age = Age + 1\u000b WHERE<\/span> Age > 10<\/p>\n

Deleting<\/strong><\/p>\n

Deleting tuples satisfying a condition:<\/p>\n

DELETE<\/span> FROM<\/span> Students\u000b WHERE<\/span> name = “Harry Potter”<\/p>\n

Deleting a table:<\/p>\n

DROP TABLE<\/span> MM13 ;<\/p>\n

Deleting all data inside a table but preserving the table structure:<\/p>\n

TRUNCATE TABLE<\/span> MM13 ;<\/p>\n

You can find an extensive list of SQL commands and examples of how to use them here<\/a>. The help page <\/a>for Access is also very informative.<\/p>\n","protected":false},"excerpt":{"rendered":"

The Structured Query Language, SQL, is a standard language to manipulate databases. There are four basic operations of databases: Create, Read, Update and Delete (CRUD). The required SQL statement for each operation depends on the data object(s) handled. Here is a summary of the most common ones: Operation SQL Statement Description Create CREATE TABLE\u2026 Creates […]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":74,"menu_order":2,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-83","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=83"}],"version-history":[{"count":27,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/83\/revisions"}],"predecessor-version":[{"id":1533,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/83\/revisions\/1533"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/74"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}