Top 11 SQL queries that a data scientist should know

A relational database management system uses SQL for managing data and for storing it in tabular form. SQL stands for Structured Query Language and it is a query language that is extremely useful for data scientists.

If you want to learn data analytics and get a data science certification course done for your progressive career, SQL is absolutely essential.

Here are some essential SQL queries for data scientists. Data scientists need to be able to retrieve and store the data which you have derived without the help of others.

Must know SQL queries for a data scientist

A data scientist must know the following basic queries to store and manage data in a tabular form in SQL. These commands may be enumerated as follows:

For creating databases

Before creating a database you first need to check what are the databases that are already present. For that, the command is:

mysql> SHOW DATABASES;

Fresh databases can be created with the understated command:

mysql> CREATE DATABASE medium;

For creating tables

Tables can be created in databases with the following command. Here is an example of creating a table for students: 

mysql> CREATE TABLE (Student [table name]) (

    -> Id int primary key,

    -> Name varchar(20),

    -> Subject varchar(20),

    -> registered char(1)

    -> );

The primary key establishes that each row is uniquely identified.

Delete a table

For deleting a database, you will have to use this command:

mysql> DROP DATABASE medium;

Add new columns

New columns can be added using the 'alter table' statement and using the following command:

mysql> ALTER TABLE student ADD gpa DECIMAL(3,2);

Delete a column

A column can be deleted using the 'alter table' statement and using the following command:

mysql> ALTER TABLE student DROP gpa;

Describe statement

if you want to ascertain a specific table’s overview, then the following command will be used:

mysql> DESCRIBE student;

Add a new row

After the creation of the table, you need to add rows and values to it otherwise it will remain empty. Rows can be added to tables by using the following command:

mysql> INSERT INTO student VALUES(1, "mary", "accounts", "F");

mysql> INSERT INTO student VALUES(2, "Anderson", "Mathematics", "F");

mysql> INSERT INTO student VALUES(3, "George", "Physics", "S");

mysql> INSERT INTO student VALUES(4, "Natalie", "Biology", "S");

The values have to be in accordance with the predefined data type.

Delete a row

If you are trying to delete a row then the 'delete from' command has to be used in the following way:

mysql> DELETE FROM student (table name);

WHERE (condition);

The condition for deleting the row must be declared after the 'where statement'.

Update a row

If any update or addition is required in the rows that already exist, that can also be made in the tables. This command must be used for the same:

mysql> UPDATE student SET subject= "Accounts" WHERE Id = 2;

The update must be specified after the keyword 'set' and the condition has to be specified after the keyword 'where'.

Select a row or rows

Queries are run so that data can be retrieved from databases and the 'select statement' is used for that purpose. You can specify the row or the column that you need to retrieve data from, using the 'select command' in the following manner:

mysql> SELECT Name, Subject FROM student (table name);

Select based on a condition

To retrieve the data from all the rows while applying some filters and conditions to the rows to make the data analysis more precise, you should use  the following command needs to be used:

mysql> SELECT * FROM student WHERE Subject = "Mathematics";

Conclusion

Learn data analytics to get a hang of the most important SQL queries to become a data scientist. Enrol in the Data Analytics course with placement by Imarticus and it will act as a launchpad for your career.