A Database Management System (DBMS) is a programme or an entire computer system used for managing a database. Apart from ensuring access to data in the database, it enables performing many operations that are necessary to ensure the correct operation of the database, such as: administrationadministrationadministration, ensuring security, optimisingoptimisingoptimising, reportingreportingreporting or a range of additional mechanisms.
Task 1
R1TtTbd2nPKiD1
On the Internet, find popular Database Management Systems.
R2pvNavbxU0G71
Structured Query Language (SQL) is a language used for creatingcreatingcreating and modifyingmodifyingmodifying databases and for insertinginsertinginserting and retrieving data from databases. It enables performing the following basic operations:
creating and modifying databases,
creating, modifying and deletingdeletingdeleting tables,
inserting, modifying and deleting data in tables,
searchingsearchingsearching for data in tables (queries).
Most database programmes installed on desktop computers include mechanisms that enable performing these operations without knowledge of the SQL syntaxSQL syntaxSQL syntax.
R2Fg9E595cuEX1
The easiest method of insertinginsertinginserting data into a database is importing the data from the existing data sources directly to tables in the database.
The INSERT statement in SQL is used to insert data into a database table.
Task 2
RsPZEk7owOakn1
Download the file and import the file with the data to a database.
R1bqlsJwV9Llq1
R1XELhsqQLKSO
RUt6Gd7NZv2TQ1
In the resulting table you will find the information about the books: their author, title and the year of their first edition. Add five records with your favourite books.
RIH5xSQZF8WbU1
ModifyingmodifyingModifying data refers to changing the content of fields in existing records. To modify (update) data in SQL you must use the UPDATE statement.
Task 3
Rg9M2N8Dt5sng1
In the table from the previous task change the data in a selected record.
R1IHlRyZFl9NW1
DeletingdeletingDeleting data refers to removing an entire record or a group of records in a table. To delete data in SQL you must use the DELETE statement.
Task 4
RFPAnlB3hgtCP1
In the table from the previous task, delete two selected records.
RdThlq5HI9jCu1
One of the operations performed the most frequently on databases is searchingsearchingsearching for data. A statement in SQL that realises queries to a database is the SELECT statement.
Task 5
RRrmfG1iG30oY1
In the table from the previous task, find the books meeting the following criteria:
issued after 2010,
the Author field begins with the letter ‘C’,
in the Title field, the second letter is ‘i’ .
If you have problems with the task, see the solution.
R139aVFj3fxrL1
R15n8P1NpTeqX1
Data in the form of records are entered into the tables of a database in an unorganized way. To order them, we usually sort a selected column of the table in an ascending or descending order. A statement in SQL that realises sortingsortingsorting tables is the SELECT statement together with the ORDER BY statement. It performs sorting data, while its parameters defining the sort order are: ASC – in an ascending orderin an ascending orderin an ascending order (alphabetically) and DESC – in a descending orderin a descending orderin a descending order.
Task 6
R1T7GbF0wR0sG1
In the table from the previous task, sort the books from the one issued the least recently.
If you have problems with the task, see the solution.
RE8ssgILKuvAd1
RPLA3ywuLM36w1
Databases are used for storing truly huge amounts of data. Thanks to them you can easily search for needed information and modify it.
Exercises
RJDHv2fzzgOsO
Exercise 1
Exercise 2
In the database you worked on during the lesson perform the following operations:
find the books issued last year,
find the book titles which contain the word ‘the’,
sort the records in the table in a descending order by the Title field,
sort the records in the table in an ascending order by the Title and Author fields.
Exercise 3
Consider what database would be useful to you on an everyday basis. Why? Design the needed tables.