In order to perform certain operations on existing databases, the Structured Query Language (SQL) is always used. In order to carry out the tasks performed in the database, certain commands such as CREATE, DROP, INSERT, ALTER, DELETE, etc. are used. SQL is really the computer language, used by database administrators, in order to design, develop and manage databases. When database administrators use SQL, they can execute queries against the database and they can also insert, delete, update and retrieve data from the database. Databases and tables can be created using SQL, and security updates can be performed on the database, using SQL. The main categories of SQL are:
DDL which stands for data definition language helps to manage and develop the structure of the database. Indexes, tables, views, and triggers, are created, removed and updated, through DDL statements. One of the DDL commands is the CREATE command, which is used mainly, to create tables in the database.
In the example below, I create the PATIENT_RECORD table using the CREATE command, I will be using on my project.
CREATE TABLE PATIENT_RECORD
patient_record_ID int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
medical_condition varchar (250) NOT NULL,
allergies varchar (250),
updated_date DATE NOT NULL,
diagnosis varchar (250) NOT NULL,
patient_ID int FOREIGN KEY
Another one of the DDL commands, which would also be used on the project, which I would be working on is the DROP command, which would be used in the project in order to delete tables. The DROP command, of course, is used to delete entire tables, from the database.
The example below will use the same table used above, PATIENT_RECORD in order to demonstrate, what the DROP command would look like.
DROP TABLE PATIENT_RECORD;
The ALTER command is another DDL command set, and it is used to add, drop and modify columns in a table.
In order to show an example, the PATIENT_RECORD table is used, and the table will receive a new column known as blood_type.
ALTER TABLE PATIENT_RECORD
ADD COLUMN blood_type VARCHAR (75) NULL;
DML is another type of SQL commands and it stands for “Data Manipulation Language” because the data that is stored in database tables, can be modified. In order to manipulate, store, retrieve, delete, and update the data, in a database the SELECT (data is retrieved), INSERT (data is uploaded to the tables), DELETE (all records are erased from the tables), UPDATE (data is changed within the tables), etc…. are the set of DML statement used.
The following example is using the table I am using for my project, we are going to continue to use PATIENT_RECORD
- SELECT is the most used SQL statement because users are able to retrieve data from tables in a database.
Retrieves data in the entire table.
SELECT * FROM PATIENT_RECORD;
Retrieves data in specified values of the table.
SELECT (patient_record_ID, medical_condition, allergies, updated_date, diagnosis, patient_ID
) FROM PATIENT_RECORD;
- INSERT is used to load up the tables, new rows are created in a table, through this SQL command, to store data in the database.
INSERT INTO PATIENT_RECORD (medical_condition) VALUES (“Kidney Stones”);
- DELETE can be used to get rid of unwanted tables of a database, sometimes faulty values can be uploaded to database tables; therefore, these data from these tables need to be removed from the databases.
DELETE * FROM PATIENT_RECORD;
DELETE FROM PATIENT_RECORD WHERE patient_ID = ‘1’;
- UPDATE database administrators are able to modify data within tables of a database when they use this type of SQL command.
UPDATE PATIENT_RECORD SET medical_condition = “High Blood Pressure” WHERE patient_ID = ‘1’;
DCL: in order to control privileges in tables of a database, these set of SQL types of commands are used, DCL stands for Data Control Language. The two types of privileges a user can have are: system permissions, which allow users to create sessions and tables. The other type of permission is the object, which allows users to perform all types of query commands.
In order to provide access privileges to any users to database tables, the GRANT command is used, so that users can perform any operations on the databases. The example below illustrates the GRANT command in use, to give permission to user hcadet, to create tables.
GRANT CREATE TABLE TO hcadet;
In order to revoke the privileges from the user hcadet, the REVOKE command is used, and it is illustrated in the example below, where user hcadet loses privileges to create tables, in a database.
REVOKE CREATE TABLE FROM hcadet;
The ERD of my project is below, you will find the table I am using for the project, let me know if you can see it well thank you.
Data from multiple tables can be stored in views, because these are virtual tables, which can be created, in order to access data that is stored in other tables. A predefined SQL query is used, in order to form the views; all rows or certain rows of tables can be included in views. The written statement query would determine whether one or multiple tables are used, in the views. Users can use views to restrict access to certain data so that users only see what they are looking for. Views also allow users to structure data so that users can have a better user experience and find the data intuitive. Users can also generate reports, through views, because data can be summarized with views.
CREATE VIEW is the SQL statement used, in order to create views, which can be created using a single or multiple tables, as well as other views. The appropriate system privileges must be granted to a user, who wants to create views in a database.
CREATE VIEW PATIENT_VIEW AS SELECT fname, lname, age FROM PATIENT;
Now we can select data from PATIENT_VIEW, to only see the patient’s full name and their age from the view we have created.
SELECT * FROM PATIENT_VIEW;
Thank you for reading this article!!!