The following SQL “program” creates the table shown in the example below:
CREATE TABLE composers (id INTEGER PRIMARY KEY, name CHAR(20), address CHAR(50)); INSERT INTO composers (id, name, address) VALUES (1, 'Beethoven','23 Ludwig Lane'); INSERT INTO composers (id, name, address) VALUES (2, 'Dylan','46 Robert Road'); INSERT INTO composers (id, name, address) VALUES (3, 'Nelson','79 Willie Way');
ID
NAME
ADDRESS
1
Beethoven
23 Ludwig Lane
2
Dylan
46 Robert Road
3
Nelson
79 Willie Way
The column “id” is designated to be the “primary key” of the table. This means that each row may be uniquely identified by using this column. From now on, the system will guarantee that the value of “id” is unique and it always exists (that is, it has the NOT NULL property).
If Mr. Dylan moves to 61 Bob Street, you can update his data with the command:
UPDATE composers SET ADDRESS = '61 Bob Street' WHERE ID = 2;
Because the ID field is unique for each composer, and because the WHERE clause in this command specifies only one ID, this update will be performed on only one composer.
If Mr. Beethoven dies and you need to delete his record, you can do so with thecommand:
DELETE FROM composers WHERE ID = 1;
Finally, if you would like to list all the composers in your table, you can use thecommand:
SELECT id, name, address FROM composers;
Note that the SELECT statement, unlike the UPDATE and DELETE statements listed above, did not include a WHERE clause. Therefore, the command applied to ALL records in the specified table. Thus the result of this SQL statement is to select (and list) all of the composers listed in the table.
ID NAME ADDRESS 1 Beethoven 23 Ludwig Lane 2 Dylan 46 Robert Road 3 Nelson 79 Willie Way
Note that although you entered the strings with quotation marks, they are displayed without quotation marks.
The above simple commands help show some important points about SQL.
▪SQL is a relatively “high level” language. A single command can create a table with as many columns as you wish. Similarly, a single command can execute an UPDATE of almost any complexity. Although not shown here, you can update multiple columns at a time, and you can even update more than one row at a time. Operations that might take dozens, or hundreds, of lines of code in languages like C or Java™ can be executed in a single SQL command.
▪Unlike some other computer languages, SQL uses single quotation marks to delimit strings. For example, 'Beethoven' is a string. “Beethoven” is something different. (Technically, it is a delimited identifier, which is not discussed in this section.) If you are used to programming languages like C, which use double quotation marks to delimit strings (character arrays) and single quotation marks to delimit individual characters, you will have to adjust to SQL’s way of doing things.
Although the example above does not clearly show it, there are several additional points you need to know about basic SQL:
▪Although SQL is a very powerful high-level language, it is also a very limited one. SQL is designed for table-oriented and record-oriented operations. It has very few low-level operations. For example, there is no direct way to open a file, or to shift bits leftward or rightward. It is also hardware-independent, which is both an advantage and disadvantage. You have very little control over the format of the output from SQL queries; you may choose the order of thecolumns, and by using the ORDER BY clause you may control the order of the rows, but you cannot do things such as control the size of the font on the screen, or print page numbers at the bottom of each printed page of output. SQL simply is not a complete programming language such as C, Java, PASCAL, and so on.
▪Each SQL implementation has a fixed set of data types. The data types in solidDB® (and most other implementations of SQL) include INTEGER, CHARacter array, FLOATing point, DATE, and TIME.
▪SQL is generally an “interpreted” language rather than a “compiled” language. To execute one or more SQL statements, you typically execute a separate program that reads your script and then executes it. No “compiled program” or “executable” is generated and stored for later use. Each time you run the program, it is interpreted again. (Stored Procedures can be reused without necessarily reinterpreting them. Stored Procedures are discussed briefly in System stored procedures and extensively in SQL extensions.
▪Table and column names are case-insensitive in SQL. In our examples, keywords (such as CREATE, INSERT, SELECT) are capitalized, and table and columns names are shown in lower case. However, this is only a convention, not a requirement.
▪SQL is also not very picky about whether commands are written on a single line or are split across multiple lines. There are examples of multiline statements later in this section.
▪SQL commands can get extremely complicated, with multiple nested “layers” of queries within queries. Figuring out how to write a complex query can be quite difficult - and figuring out how to understand a query that someone else wrote can be equally difficult. As in any programming language, it is a good idea to document your code!
▪To help you document your code, SQL allows “comments”. Comments are only for the human reader; they are skipped over by the SQL interpreter. To create a comment, you have two options:
– Line comment: put two dashes (--) at the start of the line and end the comment with a line break. The comment cannot extend to a new line.
– Block (multiline) comment: Begin the comment with a slash and an asterisk (/*) and end the comment with an asterisk and a slash (*/).
All the subsequent characters up to the end of the line will be ignored. (There is an exception for “optimizer hints”, another advanced topic that we will not discuss in this section.)