- Structured Query Language (SQL) - Part Two
Structured Query Language (SQL) - Part Two
In your journey to becoming a great programmer, you will encounter those moments, when you doubt yourself, and when you think that you can’t do the job.
It is okay to doubt yourself though, because you are supposed to doubt yourselves and believe that you can’t accomplish anything, in the area of study that you are in.
However, you can’t give up, and you can’t give in, and eventually you need to trust in your skills, even when you don’t believe you have them, because the situations that you are in, were made for you, and you for them, to help you become a better developer.
The obstacles that you face in life, were made to make you stronger and to elevate you to another place.
Don’t just ignore your failures and tight situations, because they are going to help you, to reach another level.
In today’s article we are going to talk about a programming language that is used to manage your data, once it is stored in a data base.
When you learn to master this programming language, you’ll be able to perform queries, which would amaze your users.
Today we are going to talk about structured query language (SQL), and all the great things that it can do for your programming skills, when you learn to master the features of this language.
You have to master this programming language though, if you would like to create great apps, which can change the life of yours and your clients.
SQL allows you to access and manipulate your databases, and it is the standard language used for that purpose.
If you don’t know SQL, then you will have a hard time, trying to create apps, which can be used in the real world, and are connected to a database.
When you are able to write SQL statements, you will be able to create queries against a database, to retrieve data from a database, to insert records in a database, to update records in a database, to delete records from a database, to create tables in a database, to create stored procedures in a database, to create views in a database and to set permissions on tables, procedures and views in a database.
The tables in your database is a collection of related data entries, which consists of columns and rows, which contains all the data that you would like to load, either from another database, or from an existing spread sheet.
You can also use database tables, to collect information from a website or an application that can connect to the cyber world.
Databases usually contain one or more tables, depending on how big your website and data is, if you have a lot of data then your database, will contain a lot of tables; however, if your data or website or the data you are collected is not as much, then you won’t have a lot of tables.
These tables can also relational, which most databases are, but there are other type of databases such as hierarchical model, network model, object/relational model, object oriented model, semi-structured model, associative model, and entity-attribute-value (EAV) data model.
We are not going to go deep into each type of databases but we can touch just a little bit, on each kind of databases.
The relational database (RDBMD – relational database management system) stores all data in relations, which are tables, with rows and columns to the user.
In a hierarchical model, the data is in a one to many relationship design; in a tree like structured is used to store the data.
Therefore, there is a hierarchical parent and a child data segment.
The other model is the network model, which allow the same owner file to have multiple records.
The network model is an enhanced hierarchical model, which represents data in a tree of records.
Some entities can be accessed through several path, in a network model.
Then there is the object/relational model, which at the core of modern information system, it adds new object storage capabilities to the relational systems.
Developers are able to integrate databases with their data types and methods, when they use the object-relational model, which is designed to provide that type of relational database management.
The object-relational model is essentially a relational model that allows users to integrate object-oriented features into it.
The next model is the object oriented databases, which are also called Object Database Management Systems (ODBMS).
Object databases store objects rather than data such as integers, strings or real numbers.
Objects are used in object oriented languages such as Smalltalk, C++, Java, and others.
In semi-structured data model, which is sometime called “self-describing”, the information that is normally associated with a schema is contained within the data.
Then there is also the associative model, which works with entities that have a discreet independent existence, and their relationships are modeled as associations.
Alright, that’s is for right now, we’ll talk about the other models in more detail in other articles.
Now let’s get back to the fact of the matter, depending on the necessities that you have for your program or database, you will use stored procedures.
Stored procedures is a group of Transact-SQL statements, compiled into a single execution plan, which are very useful, if you know what you are doing.
Across applications, a consistent implementation of logic can be achieved, through the use of stored procedures, but you need to know what you are doing, if you don’t want to break things.
You can use a stored procedure, to design, code and test a task, which is commonly performed by an SQL statement and logic.
Once the stored procedure has been set up, then you can go ahead and allow other applications to use that same procedure.
This way you are not writing SQL statements over and over again, for multiple applications, you can just go ahead and use the same stored procedure to perform the same task on the same database, remember though, you need to know what you are doing, before you use this SQL feature, which is very useful.
Stored procedures, can also help you with performance, because many tasks which are implemented as a series of SQL statements, can be part of a single execution plan on the server.
Users are also shielded from having to know all of the details of tables in a database, when they utilize stored procedure.
Stored procedures have the ability to access input parameters and return multiple values in the form of output parameters.
Stored procedures can also contain programming statements, which can perform operations in the database.
Once you have created your stored procedure and store it in a database, you can use it multiple times, by multiple applications, which connect to that same database as we mentioned before.
Also if you would like to modify a stored procedure code, you don’t have to worry about modifying your application’s code, because the application’s code, is written independently from the stored procedure’s code.
Stored procedures can be complicated to understand; however, once you understand them and master them, you will be able to do great things with your database and applications.
Another great feature in databases is views, this feature allows you to store an SQL statement in a database, with an associated name.
You can think of views as either a virtual table or as a store query.
You can accomplish great things, when you learn to use views, but you’ll have to master them and understand them, before you are able to use them.
Views allow you to store the most common used SQL statements, such as SELECT.
You can also use view to restrict users from accessing specific rows in a table within your database.
You can also restrict users from accessing specific columns in a table within your database; if you for example want employees of a company to see certain information from a table such as, their personal information, but you don’t want them to have access to the rest of the information on the same table, then you can use views to accomplish that task.
Tables can be also joined, so that they look like a single table, using views.
When you define the SELECT statement, which retrieves the data to be presented by the views, then you are at that point creating a view.
The base tables for the views, are the data tables referenced by the SELECT statement.
You will be able to perform many other tasks with your database tables using views, but you have to make sure you have a full understanding of views.
As you can see you are going to have the ability to do many things in a database, once you have mastered SQL and the many other features that comes with this structured query language.
Also SQL is not case sensitive, which means that SELEC is the same as select, so you don’t have to worry about the case you use in SQL, as in other programming languages.
When you use a relational database management system (RDBMS), you would use, SQL to manage you data.
RDBMS are used to create database driven websites, and some of the most used RDBMS, which exist today are MS Access, SQL Server, Oracle and MySQL.
You would also have to use a side scripting language, such as PHP or ASP to make the necessary connections, from your SQL statements to your website or application.
Its “Query” functions are used, for searching data in a relational database.
Sometimes, you may hear people call this programming language, SEQUEL, which stands for structured English query language, but this name has been replaced with SQL.
IBM designed SQL, in 1975, and was introduced as a commercial database, by Oracle corporations.
SQL is the favorite language, for managing database systems, you can use it on minicomputers, mainframes and pc computers database systems.
When you are working with complex systems, it is always a good idea, that you get things right, at the beginning of your system set up.
Once you have started working on an app, but your system was not set up the right way, you will find yourself starting all over.
You may want to watch out for that, starting over is not fun in programming, so make sure that your system is set up right, before you jump into programming.
Don’t get ahead of yourselves, or else you will have to start over, trust me, especially if you don’t know the system you are using.
Alright let's get back to the fact of the matter, SQL, which is a system that can grow in complexity, when you don't know what you are doing.
When you write SQL statements you will find that you need a semi column after every SQL statement for some database systems.
However a semi column is the standard, for separating SQL statements, and you will see them a lot when you are programming.
Some of the most commonly used SQL statements are: SELECT, UPDATE, DELETE, INSERT INTO, CREATE DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX AND DROP INDEX.
If you want to select data from a database, then you would use the SELECT statements, this way SELECT column_name, column_name1 FROM table_name or SELECT * FROM table_name.
The (*) in a SQL statement simply means all, so for this case you would be selecting all the data from your table.
The SELECT statement has many variations and conditions that you can apply to it to make it more complex.
Such condition could be DISTINCT, which you can apply to an SQL statement when you want to collect data from a table which contain multiple rows and columns with the same type of data, and you want to only collect the data which is unique in that table.
Alright, we have reached the end of this article post, but not the end of this conversation, we’ll cover the rest of SQL on other article posts, so be on the lookout.
Thank you for reading this article!!!
Add a Comment
Hi every one, I obtained a bachelor's degree in Bioinformatics back in 2006, from Claflin University, after I received my bachelor's degree, I gained full time employment as a software engineer at a Video Relay Service company, maintaining databases and developing software for a new developed device called the VPAD.
I worked at that company for two years, then I became a web developer, and worked for a magazine for three years. After that job, I worked as a Drupal web developer, as a subcontractor for the NIH, for a year and then left the job to go back to school.
Collaboratively administrate empowered markets via plug-and-play networks. Dynamically procrastinate B2C users after installed base benefits. Dramatically visualize customer directed convergence without
Collaboratively administrate empowered markets via plug-and-play networks. Dynamically procrastinate B2C users after installed base benefits. Dramatically visualize customer directed convergence without revolutionary ROI.