SQL: Basic concepts - University of Edinburgh
SQL: Basic concepts
? SQL operates with tables, so the first thing to do is create tables. ? Syntax:
CREATE TABLE ( type, ..., type>)
? For example:
CREATE TABLE Movies (title char(20), director char(10), actor char(10))
CREATE TABLE Schedule (theater char(10), title char(20))
Database Systems
1
L. Libkin
Types
? char(n) ? fixed length string of exactly n characters. Example: 'Polanski'
? varchar(n) ? variable length string of up to n characters. Example: 'Polanski'. What's the difference? We'll see soon. Note: varchar is actually an abbreviation for char varying.
? bit(n) ? fixed length bit string of exactly n bits. Example: B'0101', X'C1'
? bit varying(n) ? variable length bit string of up to n bits.
Database Systems
2
L. Libkin
Types cont'd
? int ? signed integer (4 bytes) ? smallint ? signed integer (2 bytes) ? real ? real numbers. ? In fact, there is a general float type float(s), and real is float(s)
where s is implementation defined. ? SQL has many more types, such as date, time, timestamp, character
sets in different alphabets, etc.
Database Systems
3
L. Libkin
Types cont'd: Dates and Times
? date type: keyword DATE followed by a date in an appropriate form, e.g. DATE '2001-12-14'
? time type: keyword TIME followed by a string representing time; SQL uses the 24-hour clock.
? timestamp type: combines date and time. For example, TIMESTAMP '2001-12-14 11:28:00' is 11:28am on December 14, 2001.
? Operations on these types: they can be compared for equality, and for order. If for two dates d1 and d2 we have d1 < d2, then d1 is earlier than d2.
Database Systems
4
L. Libkin
Populating tables
? General syntax:
INSERT INTO VALUES (...) ? Examples:
INSERT INTO Movies VALUES (`Chinatown', `Polanski', `Nicholson')
INSERT INTO Schedule VALUES (`Odeon', `Chinatown')
? More generally, one can use other queries for insertion:
INSERT INTO Name (SELECT ... FROM ... WHERE ...)
as long as the attributes in the result of the query as the same as those of Name.
Database Systems
5
L. Libkin
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- constraints types of sql constraints duke university
- cs1307 database management systems lab manual
- create table students virginia tech
- sumita arora xii structured query language
- oracle sql tutorial emory university
- sql to hive cheat sheet cloudera
- defining relational schema cs 2451 database systems
- the relational model carnegie mellon school of computer
- sql data types and schemas integrity constraints
- chapter 4 advanced sql date and time data types in sql
Related searches
- 4 basic concepts of development
- basic concepts of information systems
- development of basic concepts list
- the basic concepts of information systems
- describe the basic concepts of information systems
- basic concepts of education
- basic concepts and types of information systems
- basic concepts of information system
- basic concepts of evolution
- basic concepts of chemistry pdf
- basic concepts of mathematics pdf
- basic concepts of statistics pdf