SQL coding language database search: The coding languages which are used to search a Database. A Database simply put, is a large collection of data stored in a large hard drive. There is a certain logic set behind why the Database is organized the way it is and why the data is kept in tables the way it is. Needless to say, a database is created with a logic that will make it work the most optimal. SQL or Structured Query Language is a tool that allows us to search a database and retrieve and manipulate the data which we are searching for. It also helps Create, amend or delete tables which the data will be saved in.
to learn how to use SQL one must first understand the Logic behind the language and how it works. It is the understanding of how the tables are linked to one another that allows a more complicated search to be done using the tool.
To start with we must get a simple understanding of Data. Data is simply put, ‘raw’ fact such as telephone number. There are two main Data Types, Primitive Types, and Composite Types. Primitive types are the basic building blocks of most programming languages. They include numbers, Strings, and Booleans (0 and 1). Composite types are a bundling of elements together and placed next to each other in the memory. Examples include an array (Fixed size sequence of elements stored contiguously) or Records (person name, age, nationality). A Data Structure is a Composite Type Data or a group of Composite Type Data associated by references.
in SQL all data is saved onto tables, these tables are made up of Columns and Records (rows). When being made the rows columns are given Data Types meaning only a certain data type can be saved into it. It is important to know this because for example when searching a column which saves telephone numbers which is an Int (integer) data type, one cannot search for letters. This shall be explained further when SQL examples are made.
Some other Data Types which columns have in SQL include:
- Int, tinyint: these two are used for numbers. tinyint is only 1 byte of storage which means 0-255
- char, varchar, text: used for characters in English. Char has a fixed length whereas Varchar allocates what is needed. if I allocate 10 bits to a char cell then I only have 10 spaces too but the letters. Useful when giving the limit to cells.
- n char, text: used for Unicode characters or international characters like Japanese or Arabic.
- date, datetime: used for Date and Time.
In order to retrieve the data which is needed, it is important to identify whether the data is in a single table or not. For example, if there is a Hotel table in the database and we are trying to find the ‘Name’ and ‘HotelID’ we must first identify if both of these are kept in the Hotel table. If they are then it a simple query will be able to retrieve the data. However what if we also additionally want to retrieve the address for all of the hotels in the Hotel table. If the Address for the hotels is saved on a different table then we need to somehow connect the two tables and retrieve the desired data. in such a case a common denominator needs to be found between the two tables. There must be a key that individually connects the two tables. for example in the Hotel table there may be an AddressID column, this Address ID if entered into the Address table, shows the address which that ID corresponds to. Since we do not need the AddressID and it would be meaningless to us, we simply need to use it to connect to the Address table and retrieve the correct address for each hotel. This connection is called a join. There are four different types of join that can be used and they are as follows:
- Inner Join: this join only connects the data which is shared by both tables
- Left Join: this join connects all the data from table 1 and only the shared data from table 2
- Right Join: this join connects all the data from table 2 and only the shared data from table 1
- Full Join: this join connects the entire data from both tables 1 and 2.
Using the correct Join commands helps to retrieve the data in the right way. however the most common join is the Inner join, and we shall show many examples of queries written using it. The following will be examples of simple SQL queries and their functions.
Grouping; SQL-Brings info from rows
SQL commands
- Select
- Top-How Many
- Count-Column Name
- From-Table Name
- Group by-Column by which you want
- Order by-Columns you want to sort by
- Option-MaxDop 1