5 min read

Introduction To SQL for Developers

Introduction To SQL for Developers

There is no denying that data rules our society today. The internet permeates everything we do, and one or more databases are hidden behind every website. Developers and data scientists can edit, retrieve, and maintain the data in this database using SQL. SQL is thus quite helpful.

This article will discuss SQL, an introductory guide to SQL, a very essential and helpful tool to developers, and the roles and responsibilities of SQL developers.

TABLE OF CONTENTS

Introduction

What is SQL?

What is Database?

Types of database

What is a Database Model?

SQL data types

Roles and responsibilities of SQL developer

Common command in SQL

SQL keywords while writing queries

Conclusion.

SELECT hello

FROM greetings;

Introduction

As a Developer or a Data scientist, you must have heard of SQL(sequel) From your learning roadmap to attending Bootcamps. SQL can be pronounced as “sequel” or said as an acronym “ess-que-el.”  Generally, Both SQL developers and SQL skills are in demand. Also, web developers that add SQL as a bonus skill are also in high demand. but there is a shortage of people with those skills. Therefore mastering SQL is a great opportunity to advance your career. Let's jump right to business.

‌

What is SQL?

SQL stands for Structured Query Language. SQL is a popular programming language for Developers and most of the world's data are in databases, SQL is the  language for interacting or communicating with databases and is also the industry standard for relational database systems (RDBMS)

What is a Database?

A Database is a collection of information organized so it can be easily accessed, updated, and managed. A database contains tables, graphs, and columns to display data. Databases guarantee that data is accessible by storing it digitally.

Types of Database

Relational Database -  sets of data stored in a table that is linked to each other using relationships. A popular database model is a relational model, which uses a table-based format.

What is a Database Model?

A particular kind of data model called a database model establishes the logical organization of a database. It dictates how data can be organized, stored, and processed.

Other types of models include

Document (JSON/XML)

Key-Value

Wide Column

Graph

Time Series

A tabular Database - is a set of data organized into tables. A table can be thought of as a spreadsheet but it's far more complex.

SQL Data Types

Categories of Data

Data can be categorized into 3 categories based on their format.

• Structured - Structured data, often known as "Quantitative Data," is data that possesses a clearly defined structure.complies with a data model that is accessible and usable with ease

The percentage of structured data is only around 20%.

• Unstructured - Unstructured data, often known as "Qualitative Data," is data that cannot be analyzed using standard techniques because it lacks a predetermined data model.

An estimated 80% of data is unstructured.

• Semi-Structured - The "bridge" that connects structured and unstructured data is semi-structured data.

It is more complicated than structured data and easier to store than unstructured data, but it lacks a predetermined data model.

In SQL we have three main data types

• Numbers: salary, commission rate.

• Dates: hire, order dates, ship dates.

• Strings: character- first & last name, emails.

SQL is very close to English, which makes it easy to understand and has three main advantages.

•SQL allows you to access many records at once.

•SQL allows you to group, filter, and aggregate these records at once.

•SQL allows you to insert, update or delete records in a database, create a new database, table and retrieve data.

Career options available in SQL

If you know SQL, you are a great fit to be

• Database Developer

• Database Administrator

• Back-end App Developer

• Full Stack Developer

• Data Scientist

• Data Analyst

Roles and responsibilities of SQL developer.

SQL Developers - Some SQL Developers work as part of a team that may include backend web developers, frontend web developers, and other programmers. The day-to-day responsibilities of a SQL Developer are to

• Optimize database performance.

• Maintain a database, perform backup tests and rectify security errors.

• Design, create and implement databases to match user end requirements.

• Update, validate or authenticate databases.

Web developer with SQL skills - most companies have large databases and require web developers that can switch to database design and maintenance, employers may hire a team of web Developers with SQL skills to carry out those tasks. Their day-to-day responsibilities include.

• Designing and maintaining a database.

• Updating and retrieving data from the database.

• They are also responsible for testing the databases and addressing any errors.

Data Scientist - Data scientists are also needed as part of a team with developers, due to the large amount of data that companies produce. They analyze data to answer a business question or tell a story within the data. Their day-to-day responsibilities include:

•using SQL to access, manipulate and store data in a database.

• Extracting data for data exploration

• Create models and algorithms with data using SQL.

Several Implementation of SQL

• SQLite

• MySQL

• PostgreSQL

• Oracle SQL

• SQL Server

As a SQL Developer, you should know these important commands.

DML: Data Manipulation Language.

DDL: Data Definition Language.

DCL: Data Control Language.

DQL: Data Query Language.

DML: Data Manipulation Language.

TCL: Transaction control language.

DML: Data Manipulation Language is used for inserting, updating, deleting, and explaining plain data.

DDL: Data Definition Language is used for creating, altering, and renaming tables.

DQL: Data Query Language is used to select from a table.

DCL: Data Control Language this command is used to revoke and grant databases.

TCL: Transaction control language, this command;

COMMIT: commits the currently active transaction.

ROLLBACK: This command reverses the direction of the current transaction.

SAVEPOINT: It will continue if you set a save point.

SET TRANSACTION: It will detail the transaction's attributes.

In SQL you can select data from a table using the SELECT statement.

FROM (name of the table we want to get)

‌                   SELECT name FROM films               SQL

  • ‌

SELECT * is used to select all columns

‌  SELECT * FROM films;

SQL allows you to select multiple columns at the same time. To select multiple names from a column, simply separate them with a comma.

‌

‌SELECT name, release_year FROM films               SQL

Each query must end with a semicolon to avoid an error.

To select the first 10 rows for 2 columns

‌                   SELECT name, release_year FROM films LIMIT 10;               SQL

  • ‌

Return Sum

‌   SELECT SUM(name) FROM films;               SQL

  • ‌

To show the database from a table, a query must be written like this

‌  SHOW column FROM database;               SQL

  • ‌

How to update a table?

-- All rows

update users set updated_at = now();

-- Some rows

update users set updated_at = now() where id = 1;

How to delete a table?

DELETE FROM users where id = 1;               SQL

  • ‌

How to drop a table?

DROP TABLE films;               SQL

Note: the syntax in SQL is not case-sensitive.

Common Commands in SQL

SELECT  Select data from a database.

AS  Rename column with an alias.

FROM Specify the table we're pulling from.

WHERE  Filter query to match condition.

JOIN  Combine rows from two or more tables.

LIKE Search for patterns in a table.

CASE Return value on a specified condition.

IS NULL Return only values with the null condition.

LIMIT Limit the number of rows returned.

IN Specify multiple values when using where.

OR Combine conditions on a query one must be met.

AND Combine conditions in a query all must be met

CREATE TABLE, DATABASE, or INDEX.

DROP Delete TABLE, DATABASE, or INDEX.

ALTER TABLE Add/Remove columns from a table.

ORDER BY set order of results, using DESC to reverse order.

GROUP BY group rows that have the same value.

HAVING is used for the aggregate function.

AVG Return average of a column.

SUM Return the sum of a column.

COUNT Count number of rows

MIN Count min of column

MAX Count max of a column.

The Order of SQL Keywords while writing queries.

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Conclusion

In this article, you have learned the select statement in SQL, run SQL queries, and the responsibilities of SQL developers. You have realized that SQL is truly a language that applies to all skills and fields. Now I suggest you drop everything you're doing and spend time running and learning SQL queries.

‌             ‌

‌

Published by: