Create table in MS Access


I will show how to create tables base on the following diagram and it's textual specification.
You can do this same process by using MS Access.
In order to create a database that contains these two tables, let's use the following code after this diagram.

Description of database tables created by using Enterprise Architect CASE tool


CREATE TABLE Occupation(
occupation_code SMALLINT NOT NULL,
max_working_hours_in_week SMALLINT=40 NOT NULL,
CONSTRAINT pk_occupation PRIMARY KEY(occupation_code),
CONSTRAINT ak_occupation_name UNIQUE (name));

occupation_code SMALLINT NOT NULL,
address VARCHAR(255) NOT NULL,
given_name VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
CONSTRAINT pk_worker PRIMARY KEY(worker_id)

The tables help to implement a system that can be analyzed with the following statements.
  •   Each worker has exactly one occupation. 
  •   Each occupation is carried by zero or more workers.
Note that the SQL standard does not describe the type CURRENCY. Instead, one can use the type Decimal.

Column is_active of the table worker must contain the truth values TRUE or FALSE  and in MS access YESNO  is the name of the type that contains these values.

If one changes the occupation code in a row of the table Occupation, then the system should extend this change to the table Worker (ON UPDATE CASCADE compensating action in the specification of the foreign key constraint.

Note that Table Occupation contains reference data. which is the data that defines the set of permissible values that other data fields can use. Meanwhile, Table Worker contains master data. Master data its self represents the business objects that are agreed on and shared across the enterprise. 

SQL Question:

  1. Find the average price of flats, the minimal price of flats, maximal price of flats, the number of flats, the sum of flat prices,  the price of which is registered, and the difference between the maximal and the minimal price. In the resulting table the corresponding columns must have names avg_price, minimal_price, maximal_price, sum_of_prices, number_of_flats_with_price, range_of_prices.

SELECT Format(Avg(price),'\€#,##0.00') AS avg_price, 
Format(Min(price),'\€#,##0.00') AS minimal_price, 
Format(Max(price),'\€#,##0.00') AS maximal_price, 
Format(Sum(price),'\€#,##0.00') AS sum_of_prices, Count(price) AS number_of_flats_with_price, 
Format(Max(price)-Min(price),'\€#,##0.00') AS range_of_prices
FROM Flat;

Query Result:
Query Result
2. Find the number of hotels in Lagos. The resulting table must contain exactly one column with the name cnt
SELECT Count(*) AS cnt

FROM Hotel WHERE city = 'Lagos';

3. Find the numbers of hotels where it is at least one reservation.Duplicate lines must be removed from the result.
SELECT hotel_nr
FROM Reservation
GROUP BY hotel_nr;


FROM Reservation;

4. Find rooms that cost more than 400 naira for a night. Find data from all the columns of the table Room. In case of each found room, present also the name of the hotel where the room is situated
SELECT Room.*, Hotel.hotel_name AS hotel
FROM Hotel, Room
WHERE Hotel.hotel_nr=Room.hotel_nr And Room.price>400;

5Find the number of courses and their average number of credit points, in case of courses that comments are missing, the name ends with y, and the number of credit points is not 1. The result must be a table with one row and two columns. The names of the columns must be cnt and average, respectively.

SELECT Count(*) AS cnt, Avg(credit_points) AS average
FROM Course
WHERE Course.comments IS NULL
AND Course.course_name Like '%y'
AND credit_points <> 1;

6. Calculate the value of the following expressions:  331+ 5587  879/54  (879/54) – round the result so that there are only two numbers after comma  (233-24)*565  2^3
SELECT 331+5587 AS expr1,
879/54 AS expr2,
Round((879/54),2) AS expr3,
(233-24)*565 AS expr4, 2^3 AS expr5;

7. Find all the data about reservations. For each reservation, find also the concatenated given name and surname of the associated guest (in the column guest_name) as well as the name of the hotel where the reservation is made. Sort the results based on the hotel name in descending alphabetical order. If there are more than one reservation in the same hotel, then sort these rows based on the guest number in the ascending order. Use in the query correlation names (aliases) G, R, and H to reffer to the tables Guest,
Reservation, and Hotel, respectively.
trim(G.given_name&' '& G.surname) AS guest_name,
H.hotel_name FROM Reservation AS R ,
Guest AS G,
Hotel AS H
WHERE ((R.guest_nr = G.guest_nr) And (R.hotel_nr = H.hotel_nr))
ORDER BY hotel_name DESC, G.guest_nr;

8.Find the number of reservations in case of each hotel. For each hotel, present the hotel number, hotel name, and the number of reservations. If there are no reservations in a hotel, then the number of reservations must be 0. The column containing data values that represent the number of reservations must have the name cnt. Sort the results based on the number of reservations in the descending order.


SELECT Count(Reservation.hotel_nr) AS cnt, 
Hotel.hotel_nr, Hotel.hotel_name
FROM Hotel LEFT JOIN Reservation
ON Hotel.hotel_nr = Reservation.hotel_nr
GROUP BY Hotel.hotel_nr, Hotel.hotel_name
ORDER BY Count(Reservation.hotel_nr) DESC;

9. Find all the data of hotels (from all the columns of the table Hotel) that have no associated reservations.


FROM Hotel
WHERE hotel_nr <>ALL (SELECT hotel_nr FROM Reservation WHERE hotel_nr IS NOT NULL);

10. Insert to the table Guest_backup all the data about the guests (from the table Guest) whose address contains the word "Lagos" or whose address is not registered. Please note that table Guest_backup must contain concatenated given name and surname of guests. In case of concatenating string remove from the result leading and trailing spaces by using Trim function.


INSERT INTO Guest_backup(guest_nr, guest_name,address) SELECT guest_nr, trim(given_name &' '& surname) AS guest_name, address FROM Guest WHERE address LIKE '%Lagos%' OR address IS NULL;

11. Create a copy of the table Reservation by using a SELECT … INTO statement. The name of the copy table must be Reservation_backup


SELECT * INTO Reservation_backup FROM Reservation;

12. Define the primary key constraint in the table Reservation_backup that involves columns hotel_nr, room_nr, guest_nr, and beginning_date (in the specified order). The name of the primary key constraint must be pk_reservation_backup.


ALTER TABLE Reservation_backup ADD CONSTRAINT pk_reservation_backup PRIMARY KEY(hotel_nr, room_nr, guest_nr, beginning_date);

13. Delete from the table Reservation_backup data about all the reservations that have started more than 1500 days ago in the hotel "Sheraton".


FROM Reservation_backup
WHERE beginning_date >Date() - 1500 AND
hotel_nr IN (SELECT hotel_nr
FROM Hotel
WHERE hotel_name='Sheraton');

14. Delete from the table Reservation_backup data about all the end dates of reservations where such dates are registered. In case of all the modified rows register also a comment: "End date is removed at X", where X is the current timestamp. The comment must be appended to the existing comment. The current timestamp must be found by using a function.


UPDATE Reservation_backup 
end_date = NULL, 
comment = trim(comment & ' End date is removed at ' & Now())
end_date IS NOT NULL;

15. Modify the data of a guest with number 6 so that new address is "Lagos, Ikeja 13-05". Do it only if the guest has no reservations that have started during the last 300 days.


UPDATE Guest SET address = 'Lagos, Ikeja 13-05' 
(SELECT * FROM Reservation 
WHERE Reservation.guest_nr = Guest.guest_nr AND Reservation.beginning_date>Date()-300);

16. Find the numbers of guests who have made more than two reservations:


SELECT guest_nr
FROM Reservation
GROUP BY guest_nr
HAVING Count(*)>2;

17. Find all the data of all the guests who have made the biggest number of reservations. In addition to the data of guests, present also the number of their reservations. 


SELECT Guest.guest_nr, Guest.given_name, Guest.surname, Guest.address, Count(Reservation.guest_nr) AS cnt
FROM Guest INNER JOIN Reservation ON Guest.guest_nr=Reservation.guest_nr
GROUP BY Guest.guest_nr, Guest.given_name, Guest.surname, Guest.address
HAVING Count(Reservation.guest_nr)>=ALL(SELECT Count(*) AS cnt FROM Reservation GROUP BY guest_nr);

18. Find all the learnings that started in 1999 and finally completed with a successful exam (the result is 4 or 5). It is possible that a successful exam was done only after more than one attempt. Present data from all the columns of the table "Learning". The result must contain exactly one row for each learning that satisfies the criteria. In addition, the result must contain the given name and the surname of the student, presented as one string and a space between the names. The result must also contain the surname of the lecturer and the name of the course. Order the result based on the surnames of students in alphabetical order. If there are more than one row with the same surname, then order these rows based on the names of courses in alphabetical order.


SELECT Learning.learning, Learning.student, Student.given_name & ' ' & Student.surname AS student_name, Learning.course, Course.course_name, Learning.lecturer, Lecturer.surname AS lecturer_name, Learning.start_date, Learning.end_date
FROM Course INNER JOIN ( Lecturer INNER JOIN (Student INNER JOIN Learning ON Student.student_code=Learning.student) ON Lecturer.lecturer_code = Learning.lecturer) ON Course.course_code = Learning.course WHERE Year(start_date)=1999 AND EXISTS (SELECT * FROM Exam WHERE Exam.learning= Learning.learning AND result IN (4,5))
ORDER BY Student.surname, Course.course_name;

19. Register comment "Has to finish soon!" in case of students whose curricula code starts with the letters "MP" and who were admitted before the beginning of 1996. If there is already a comment in case of a student,  then the new comment should not replace it. Instead, the new comment must be placed in front of the existing comment.


UPDATE Student SET comment = Trim( 'Has to finish soon! ' &  comment)
WHERE Year(admission_time)<1996 AND curricula LIKE 'MP%';

No comments:

Post a Comment

Note: only a member of this blog may post a comment.

New Post

TSQL-Working With Date And Time

Here you will learn how to work with the date and time in SQL Server. select  GETDATE() as dateNow This returns the current date and time. s...