The following SQL program creates the table shown:
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 can be uniquely identified by using this column. From now on, the system guarantees 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 their data with the statement:
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 statement specifies only one ID, this update will be performed on only one composer.
If Mr. Beethoven dies and you need to delete their record, you can do so with the statement:
DELETE FROM composers WHERE id = 1;
Finally, if you would like to list all the composers in your table, you can use the statement:
SELECT id, name, address FROM composers;
Note that the SELECT statement, unlike the UPDATE and DELETE statements that were previously listed, did not include a WHERE clause. Therefore, the statement 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 previous simple commands help demonstrate some important points about SQL.
▪ SQL is a relatively high-level language. A single statement can create a table with as many columns as you need. Similarly, a single statement 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 statement.
▪ Unlike some other computer languages, SQL uses single quotation marks to delimit strings. For example, 'Beethoven' is a string.
There are several additional points you should know about basic SQL:
▪ Although SQL is a very powerful high-level language, it is also limited. 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 left or right. 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 can choose the order of the columns, and by using the ORDER BY clause you can 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 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, CHAR (character array), FLOAT (floating point number), 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, see Stored procedures.
▪ Table and column names are case-insensitive in SQL. In the examples, keywords (such as CREATE, INSERT, SELECT) are usually capitalized, and table and columns names are shown in lower case. However, this is only a convention, not a requirement.
▪ SQL is also not sensitive as to whether commands are written on a single line or are split across multiple lines.
▪ SQL commands can get extremely complicated, with multiple nested layers of queries within queries. Deciding how to write a complex query can be quite difficult - and understanding 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 usually 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 (multi-line) 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.
Note SQL also supports optimizer hints, which can be used to tune the performance of queries, see Using optimizer hints.