The views above views can be created in SQL by:
create view compStudsVw as
select id, firstname, surname
where lower(course) like '%computing%';
/* view is valid for only 1 student! */
create view myResults
The drop statement deletes a table entirely. It is more severe than an all row delete.
3.1 Simple format
drop table table-name;
Eliminate the fullResults tables.
drop table fullResults1;
drop table fullResults2;
(This is as complicated
PostgreSQL Data Types
Text: enclose within single quotes. Use two consecutive single quotes to insert a
e.g.: ('O'Brien') all single quotes.
Note: it is NOT up to the end-user to know and enter data in this way. The end user
would enter O'Br
Database Perspective: Three Schema Architecture
The three schema architecture perceives a database at three levels:
1. user or external level
- create/maintain views so users can access only the data that concerns them
2. logical or conceptual level
NOT NULL: column cannot contain NULL values
UNIQUE: values in this column must be unique within the column (often enforced by
the DBMS by creating a unique index on the column)
PRIMARY KEY: set this column as the primary key. Creates an ind
psql is a terminal based interface to PostgreSQL. psql allows users to input SQL commands
and view the results. In addition psql has a number of commands to make the task of
developing SQL easier, these include the ability to load and sa
SQL has three main statements for modification of data: INSERT, UPDATE and DELETE.
These statements are very powerful as many rows can be modified with one simple
Users, other than the database administrator, do not normally have u
Query Buffer Commands
The query buffer holds the contents of the current query (not the result) e.g. if the user just
typed in SELECT * FROM customer; at the psql command prompt then this would be the
contents of the query buffer.
In words, what do the following queries produce? Try them in the SQL interface.
SELECT SUM(paid) AS fees
Answer: The total fees paid by all students.
- Tutorial 08 -\c racers
- Question 1 - Q1: Write the SQL to add Noel as a new customer and insert the following
-order into the database, also updating the QOH:
insert into customer
values ('MCE1', 'Noel', 'McEwan', '1 High St', 'Wombat Grove');
Best usage of Views
It is usual to create views to provide data for all possibilities then be specific when selecting
from the view.
eg. A student needs to know his/her results (eg. 2 above)
Create a view to list results for all students:
create view myRe