2.3 Databases 3

Learning Outcomes:

• use Structured Query Language (SQL) to create tables and to retrieve, update, insert and delete data in a relational database; and

• evaluate Query By Example (QBE) as an alternative to SQL.

database queries

Structured Query language(SQL) and Query by Example(QBE) are both used to access and manipulate data in a database. SQL is used widely by programmers to interrogate databases efficiently. QBE is a simple query language where the user creates a query using a form. Criteria are entered into the form and the query is executed to return a set of data which fulfils the query.

structured query language

A Relational Database Management System is the basis for SQL. Consider the Student table above. The fields in the Student table are StudentID, StudentName, DateOfBirth, Email, Address, City, and PostCode. A field is equivalent to a column in the table. A record, also called a row, is each individual entry that exists in a table. For example, there are 5 records in the Student table. Each record has 7 fields. You have seen this already in the factfiles Databases 1 and Databases 2

Create a table

CREATE TABLE Student

( StudentID int not null,

StudentName varchar(255),

DateOfBirth Date,

Email varchar(50),

Address varchar(255),

City varchar(255),

PostCode varchar(8)

Primary Key (StudentID) );

select

SELECT StudentName, DateOfBirth FROM Student;

Criteria can be added to the SELECT statement SELECT * FROM Student; displays all fields and records in the table Student.

SELECT * FROM Student WHERE DateOfBirth< '1/1/1998'; displays all fields for pupils who were born before 1/1/1998

We can also order our result in a certain way as shown below. The results would be returned in descending order(Z>A) of country.

SELECT * FROM Customers

ORDER BY Country DESC;

changing DESC to ASC will return in results in Ascending order (A>Z)

update

UPDATE Student SET City= 'Belfast', PostCode= 'BT9 7YY' WHERE StudentID = 1001;

The above statement will change the value of the City and Postcode to those shown for the Student whose StudentID is 1001 (see below)

insert

INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3,etc)

To insert two records into the table use the following code:

INSERT INTO Student (StudentID, StudentName, DateOfBirth, Email,Address,City,PostCode)

VALUES ('1001', 'Anne Browne', '12/12/98', 'AB@mymail.com','1 Long Road', 'Coleraine','BT77 9PP')

INSERT INTO Student (StudentID, StudentName, DateOfBirth, Email,Address,City,PostCode)

VALUES ('1002', 'John Gormley' ,'12/5/97', 'jgormley@hitmail.com','37 Palm Ave', 'Limavady', 'BT02 7NN')

delete

Delete from Student where StudentName='John Gormley';

The above statement will delete the entry for John Gormley.

w3 schools sql

The link below takes you to a great site for practicing SQL. Use it to practise the commands explained above.

https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc

query by example

When you ask SQL Server or any other DBMS (including Access) a question about the data in a database, the question is called a query. With QBE users create queries by entering column names and other criteria using an on-screen interface, and data appears on the screen in tabular form. It is easier to create queries in this way, especially for beginners. The SQL for the query is automatically generated. SQL server provides a graphical user interface for creating and modifying tables and relationships and also for generating queries. For the purposes of this example an additional table has been added (Course) which contains courses studied by the student. This can be done by right clicking on the Table and selecting the Table option from the drop down menu.

advantages/disadvantages of qbe

QBE allows inexperienced users to make use of complex database features.

Query by Example (QBE) searches through databases filtering data based on data content graphically. This means that the user does not have to know or learn SQL.

QBE includes the language necessary to perform the searches in order to achieve results quickly using built-in logical conditions. Microsoft Access uses

QBE and can obtain results from complex search queries.

QBE can be used for to create and modify data also.

Creating a query in Access using QBE and graphical tools to set the query parameters gives a graphical representation of the query.

The SQL is automatically generated accurately and will perform an identical query.

However, it is not portable in the way that SQL is. The advantages and disadvantages of each may depend on the technical skills of the user or the time available to create the query. As SQL has industry standards well documented and is used in the relational database industry, this ensures that any query, if properly written, will give the same results across products and therefore is more reliable.

Possible Exam Questions

bi) Insert into Employee (EmployeeID,EmployeeName,JobTitle,Address,Telephone)

Values ("E123","Black","Supervisor","Omagh","32451")

b ii) Delete from Empolyee

where address!= "Belfast" or where address NOT "Belfast"

iii)

QBE: Provides a graphical/visual way of querying a database The user enters/selects values using a template/form/wizard to create a query

SQL: Is a programming language. SQL provides a set of commands to create queries

Evaluation ‘for creating queries’ QBE users require minimum technical knowledge/skill SQL programming require a high level of technical knowledge/skill Experienced SQL programmers can create queries more efficiently SQL provides more comprehensive facilities for creating complex queries

f) Insert into Actor (ActorID,Name,ContactNumber)

values ("TC2001","Tim Cruise","4455667788")

g) Select FilmID, Title from Film

Where genre ="Horror"

Order by Descending

d) Insert into Employee(EmployeeID, Name, Role, Status)

values ("M1234","John Smith","Cameraman","Full-time")

e) select name, role from employee

where status="Full time"

f) delete from employee

where status="part time"

g) QBE: Provides a graphical/visual way of querying a database The user enters/selects values using a template/form/wizard to create a query SQL: Is a programming language. SQL provides a set of commands to create queries Evaluation ‘for creating queries’ QBE users require minimum technical knowledge/skill SQL programming require a high level of technical knowledge/skill Experienced SQL programmers can create queries more efficiently SQL provides more comprehensive facilities for creating complex queries

Page updated

Report abuse