What is Database and SQL?Posted On : Aug 14, 2020 - 06:52
Last Update : Aug 14, 2020 - 08:33
Database is an important concept that has a huge place in digital world. As the name suggests, it is used to store data and almost every software today uses a database. Your profiles in social media or websites you signed up, your characters in online games, your settings and preferences etc. all of them stored in databases.
Databases are the data storage and management tools that run on a server and keep the data in tables (Relational) or can be used as a stand-alone file (Distributed) such as Microsoft Access and Excel without the need for a server. Apart from these, there are other database types, but the most commonly used one is "Relational Databases". Distributed type databases are not preferred for security and performance reasons.
Each table in relational databases consists of the columns to specify data properties and the rows to represent "new record". In addition, data connections can be established between the columns in different tables by the features called "Foreign Keys". You can find a typical database table schema which named as "Products" below.
|Product_ID (int)||Product_Name (nvarchar)||Product_Code (nvarchar)||Product_Price (money)||Product_Stock (int)|
Every single line after header (Product_ID, Product_Name...) line is a data record of "Products" table and each record has a unique number (ID) in the "Product_ID" column. IDs shouldn't be duplicated numbers because of they will be used for reach to related data, otherwise IDs will not enough to reach requested data. IDs usually being set as auto-increment for new records when designing database tables.
Databases are managed by softwares called "Database Management Systems". These softwares provide us to create, edit or delete the databases and tables and also provides list, insert, update or delete the data in the tables with the SQL commands. There are various database management systems available today; the most used ones can be listed as Oracle, MySQL, Microsoft SQL Server, PostgreSQL and MongoDB.
Let's come to the SQL. SQL (Structured Query Language) is a database control language that allows us to send data to the database tables I mentioned above, list these data in a customizable way, or perform operations such as editing (updating) and deleting them. Although it is not actually a programming language, SQL is also considered a programming language in the programming world because of its operational functions.
SQL can be used via developed aplications as well as used directly on databases. For example, when you need to insert, update or delete products from a database like above, you use the application interface that you developed instead writing sql commands one by one in the database management system. Manual SQL operations usually being used when there is no any application interface available or for the commands that can not be handle in application interface. Changing your profile picture, updating your informations, deleting-updating or sharing a new post in your social media accounts are the examples for database operations that you're doing in application interface.
You can find some SQL examples for the "Products" table below. I'll continue my articles that i'll tell more of these commands in detail.
/* Comments write inside /* */ characters. */ /* List all data (*) in table called "Products". */ SELECT * FROM Products /* Display the product name and product stock of the product which has product id is 2. */ SELECT Product_Name, Product_Stock FROM Products WHERE Product_ID=2 /* Display total (SUM) stock count of all products in "Products" table. */ SELECT SUM(Product_Stock) FROM Products /* Insert a new record (INSERT INTO) to table called "Products". */ INSERT INTO Products (Product_Name, Product_Code, Product_Price, Product_Stock) VALUES ('Disinfectant', 'PCD-01', 4, 200) /* Update the stock counts of all products that named as "Mask" and have stock count is under 100 in the "Products" table as 500. */ UPDATE Products SET Product_Stock=500 WHERE Product_Name='Mask' AND Product_Stock < 100 /* Delete all products that have no name (IS NULL) in the "Products" table. */ DELETE FROM Products WHERE Product_Name IS NULL