Date

The following statement will display the current date.

SELECT curdate() as 'Todays Date';

The following statement will display the current date and time.

SELECT now() as 'Todays Date and Time';

If you wish to format the date and display it in a more readable manner, you can use DATE_FORMAT as follows:

SELECT DATE_FORMAT(curdate(),'%d %b %y')  as 'Todays Date';

You can include commas, - etc in the string as separators.

SELECT DATE_FORMAT(curdate(),'%d-%b-%y') as 'Todays Date';

Exercise

• Output '2018-12-25' as follows:
  • December 25th, 2018
  • Tues 25th December 2018
  • Tuesday 25th December 2018
  • 25 Dec 2018
  • 25 December 18

Calculations in our database tables

DATEDIFF calculates the distance (number of days) between 2 dates.

• If we want to calculate the number of days that a book is overdue we could use DATEDIFF as follows:

SELECT loanId, DATEDIFF(curdate(), dateDue) as 'Number of Days Overdue' 
FROM loan 
WHERE dateback IS NULL;

Obviously we will need to return the title of the book overdue, and the student who the book is on loan to:

SELECT concat(fname, ' ' , lname) as Name, title, DATEDIFF(curdate(),dateDue) as 'Number of Days Overdue' 
FROM book JOIN bookcopy ON book.isbn=bookcopy.isbn 
JOIN loan ON bookcopy.copyId = loan.copyId 
JOIN student ON loan.studentid = student.studentid
WHERE dateback IS NULL;

What if we want to use SQL to calculate a value that is not stored anywhere in the database? We can just use statements as follows:

SELECT 12 + 14;
SELECT 12 - 14;
SELECT 12 * 14;
SELECT 12 / 14;
SELECT 12 % 14;
SELECT 14 % 12;

Commit and Rollback

The COMMIT statement explicitly saves previous commands up until the last COMMIT or ROLLBACK.

The ROLLBACK command will undo all statements issued since the last COMMIT or ROLLBACK.

Note: At present, all commands issued are committed automatically. To prevent this happening:

• Go to the Edit menu, choose Preferences..., this opens the Workbench Preferences dialog box.
• Select SQL Execution and uncheck the New connections use auto commit mode checkbox.
• Click OK.
• Exit Workbench and open it again.

Insert

The INSERT statement is used to add new records to tables, as follows:

INSERT INTO Book (ISBN, title, publisher, publishedDate, category, price)
VALUES ('213345432', 'Beginning XML', 'McGraw Hill', '2010-07-13','Computing', 43.00);

• Return all the book records (See the newly inserted record by issuing SELECT * FROM book;).
• Undo the above statement.

Because the above INSERT statement includes all the attributes of table Book, we could rewrite the statement as follows:

INSERT INTO Book VALUES 
('213345432', 'Beginning XML', 'McGraw Hill', '2010-07-13','Computing', 43.00);

• Again, return all the book records.
• Undo the above statement.

If you are only inserting values for a subset off the attributes, then you must specify the attribute by name. The order of the values supplied must match the order of the attribute names specified as follows:

INSERT INTO Book (ISBN, title, publisher, category)
VALUES ('214562899', 'DOM Scripting', 'Sitepoint', 'Computing');

• Again, return all the book records.
• Undo the above statement.

INSERT INTO Book (ISBN, title, category, publisher)
VALUES ('214562899', 'DOM Scripting', 'Computing', 'Sitepoint');

• Again, return all the book records.
• Undo the above statement.

If you are inserting a few records into the one table, then you can do so as follows:

INSERT INTO Book VALUES
('133451216', 'Backbone JS', 'Sitepoint', '2012-10-28','Computing', 60.00), 
('134562135', 'Sociology in Practice', 'Prentice Hall', '2010-05-08', 'Humanities', 78.00);

insert into BookCopy (ISBN, dateAcquired) values
 ('133451216','2012-12-10'),
 ('133451216','2012-12-10'), 
 ('134562135','2011-08-01');

• Return all the book and bookcopy records.
• Undo the above statements.

Notes:

• The primary key must have a UNIQUE value entered for it.
• Any values which have been specified as NOT NULL must have a value entered for them. Note: A Primary key is always NOT NULL.
• Any foreign key values must match a primary key value in the related table or else be NULL.
• String values MUST be enclosed in quotes.
• The quotes used are very specific - the ones in MS word are not the same as the ones in SQL PLUS Worksheet.

Update

Note: At present, we cannot use the Update and Delete statements without a WHERE clause. To prevent this happening:

• Go to the Edit menu, choose Preferences..., this opens the Workbench Preferences dialog box.
• Select SQL Editor and uncheck the Safe Updates... checkbox.
• Click OK.
• Exit Workbench and open it again.

The UPDATE statement is used to modify 0 or more records in a table. If the WHERE clause is omitted, then all records are updated.

SELECT * FROM student;

Note the different year values for each student. Now enter the following statement and note that all the student year values are set to 1:

UPDATE student SET year = 1;

• Again, return all the student records.
• Undo the above statement.

To update one student record (use the Primary key value in the WHERE clause):

UPDATE student 
SET year = 1 
WHERE studentId = '20026294';

• Again, return all the student records.
• Undo the above statement.

To update more than one student record (but not all records):

UPDATE student 
SET year = 1 
WHERE county = 'Kilkenny';

• Again, return all the student records.
• Undo the above statement.

Delete

The DELETE statement is used to delete 0 or more records in a table. If the WHERE clause is omitted then all records are deleted (so be careful).

DELETE FROM authorship;

• Return all the authorship records. Note that the Authorship table is empty!
• Undo the above statement.

To delete one Authorship record (use the Primary key value in the WHERE clause):

DELETE FROM authorship 
WHERE authorId = 1 AND ISBN = '123675432';

• Again, return all the authorship records.
• Undo the above statement.

To delete more than one Authorship record (but not all records):

DELETE FROM authorship 
WHERE ISBN = '123675432';

• Again, return all the authorship records.
• Undo the above statement.

Exercise

Insert

1. Add a new book to the book table:

   • ISBN:133312345;
   • title: Learning SQL;
   • publisher:O Reilly;
   • publishedDate:2009-05-08,
   • category: Computing;
   • price:25.00.
2. Add 3 bookcopies for this book, with dateAcquired:2018-09-01.

3. Add a new author to the author table:

   • fname:Adam;
   • lname:Beauleau.
4. Add a record to the authorship table, for the new author and the new book.
5. Commit the changes.

Update

1. Change the price of the book to 28.00.
2. Change the dateAcquired value to 2018-09-12 for all 3 copies.
3. Commit the changes.

Delete

1. Delete the third copy of the book.
2. Commit the changes.

Solutions

Solutions for the exercises in this lab are available here: Week5.zip