This article was published as a part of the Data Science Blogathon
Pre-requisites
– Basic knowledge of any database.
– Basic understanding of coding.
Are you ready to advance your coding skills and master the databases? Great!! Yess… you are on the right way here.
Introduction
SQL signifies Structured Query Language. SQL is a widely used language for working with data. All organizations have data, and they store at least some of the data in a database.
SQL is the language of databases- learning SQL allows you to use them effectively.
MySQL is a popular, open-source, relational database that you can use to build all sorts of web databases i.e from simple ones, cataloguing some basic information like book recommendations to most complex data warehouses, and hosting hundreds of thousands of records on. Learning MySQL is a great next step for those who have already know PHP or Perl language. In this case, you can create websites that can interact with a MySQL database in real-time and display searchable and categorized records(retrieved) to users.
SQL allows you to view data, analyze data, and perform calculations with data. With the help of SQL, you can change the data stored in a database, or change the way of the database itself works. SQL also lets you make the rules about how data may be changed and who by.
Agenda
1) How to download and Set up SQL?
2) Creating the database and connecting it
3) Statements of SQL
4) Conclusion
How to download and Set up SQL?
You will need two pieces of software: (1) SQL Server itself (2) SQL Server Management Studio (SSMS).
Microsoft provides both online for free, as a single package to download and install as well. SQL Server is the software that handles any SQL queries that you run, but it doesn’t actually display the results of queries, or appear on screen at all. SSMS is the software you will actually see that the user interface. So here you can type any queries you want to try,
and this is what displays the results.
Following is the image that shows how SSMS looks like after installing:-
Image Source: https://www.mssqltips.com/tipimages2/5174_Capture7.jpg
Now what to download exactly?
Microsoft offers several different “editions” of SQL Server online. Basically, SQL Server Express is the free one, and the one this book is based on. In the Express edition, Microsoft also offers several different download “packages”, e.g. Advanced, with tools, etc. SQL Server Express with Advanced Services that includes SSMS.
From where you will download it?
Where to download it from You can download a free version of the above from Microsoft’s website, by clicking the
download link on the web page below (if you enter “download SQL Server” on google, this page should appear near the top of your results):-
https://www.microsoft.com/en-in/sql-server/sql-server-downloads
When you click on the above link, Microsoft’s site will prompt you a message to sign in or register for an account. Once you did this, it will provide a choice of SQL Server Express packages to be downloaded. If you sign up for a new Microsoft account and it doesn’t take you directly to the download options afterwards, go back to the web page above, then click the download link, and sign in there with your newly registered account details. You will then be on the
correct page to choose your download package.
Following is the link where you can see documentation of how to download and install SSMS:-
Once you have selected the appropriate package, click on the “Continue” button at the bottom of the page. This will take you to a page that starts the download. The download is just a folder, not just one file. If your browser gives you the option to save the download, save it anywhere you want, as you know where it is.
For example, save setup on your desktop for easy access to the download. If you don’t get the choice of where to save it, it will almost certainly be downloaded to your computer’s downloads folder, typically “C:Users(your username)Downloads”.
The downloading may take some time, but it will continue by itself, so at this point, you can leave the process to finish downloading.
Creating the database and connecting it
After successfully installing SSMS, we have to create the database and we will see how to connect it to the SQL server.
To set up or create a new database use this line:
CREATE DATABASE db_name;
Now to view all your databases, we will use the following command:
show databases;
To start working with MySQL, you’ll need to establish an active SSH session on your SQL server. Connection to MySQL.
mysql -u root -p
To get rid of an unuseful database just type command as below:
DROP DATABASE db_name;
This is how you can write any queries here in SSMS:-
Image Source: https://www.essentialsql.com/wp-content/uploads/2014/08/SSMS-Run-Query.png
Statements of SQL
The following diagrams show all the required statements to implement Query.
So there are four types of SQL statements:
- Data Definition Language (DDL) Statements- DDL updates/changes the structure of the table like creating a new table, deleting a table, altering a table, and so on.
- Data Manipulation Language (DML) Statements: DML commands are used to modify or update the database. It is responsible for all forms of any changes in the database.
- Transaction Control Langauge(TCL) Statements: TCL commands can be used only with DML commands like “INSERT, DELETE and UPDATE” only.
- Data Control Langauge (DCL) Statements: DCL commands are used to grant authority or permission and take back authority using revoke from any database user at any time.
Let us first build the table in order to implement the above SQL statements:-
Working with Tables
Tables are the key element of MySQL databases as they let you store all the information together in organized rows. Each row consists of columns that feature a specified data type. You have plenty
of options for customization using the commands below.
The basic syntax of creating a table:
CREATE TABLE [IF NOT EXISTS] table_name( column_list );
The code snippet below is the features a table for a list of movies that we want to organize by different
attributes:
CREATE TABLE movies( title VARCHAR(100), year VARCHAR(100), director VARCHAR(50), genre VARCHAR(20), rating VARCHAR(100) );
To view our table:
Use the next commands to get more information about the tables stored in your database.
show tables; #call a list of all tables associated with a database.
DESCRIBE table_name; #see the columns of your table.
DESCRIBE table_name column_name; #review the information of the column in your table
How to delete any table?
To get rid of the table you need to specify the table name in the following command:
DROP TABLE table_name;
Working with Table Columns
Use columns to store alike information that shares the same attribute (e.g. movie director names).
Columns are defined by different storage types:
– CHAR
– VARCHAR
– TEXT
– BLOB
– EUT
Specify what kind of information you want to retrieve from a certain row.
When designing columns for your database, your goal is to select the optimal length to avoid
wasted space and maximize performance.
Below are the SQL commands for working with tables.
1) If you want to add any new column in the particular table then type the command below:
ALTER TABLE table ADD [COLUMN] column_name;
2) Let’s say you have some unusual column that is not required, in that case, you can either drop or delete that particular table by using the following command:
ALTER TABLE table_name DROP [COLUMN] column_name;
3) Now add a particular row or record into the table by using the below code:
INSERT INTO table_name (field1, field2, ...) VALUES (value1, value2, ...);
4) Now if we want to retrieve specific data from columns then we will write:
SELECT value1, value2 FROM table_name;
5) Want to delete a record that has been entered by mistake? yes, you can do this. Here we have used where clause that follows condition to be satisfied in order to retrieve certain record:-
DELETE FROM movies WHERE budget='1';
6) Similarly, you can use different clauses to update all or specified rows in your table.
To update all rows:
UPDATE table_name SET column_1 = value_1 WHERE budget='5';
7) You can alter any existing column with the following snippet code:
ALTER TABLE movies MODIFY COLUMN number INT(3);
8) You can mash up two or more columns together with the CONCAT function easily using the following code:
SELECT CONCAT(first_name, ' ', last_name) AS 'Name', dept FROM users;
If you’re storing important data, then you have four main concerns:
> Storing data must be quick and easy because you’re supposed to do it often.
> The storage medium must be reliable. Hence you don’t want to come back later again and find some (or all) of your data missing.
> Data retrieval must be quick and easy, regardless of how many items you store there.
> You have required an easy way to separate the exact information that you want now
from the tons of data that you don’t want right now.
Conclusion
We have seen initial steps that are required to set up the software, etc. Hope you liked this article. We will see further implementations in next article. Thank You!
I am Software Engineer, data enthusiast , passionate about data and its potential to drive insights, solve problems and also seeking to learn more about machine learning, artificial intelligence fields.
