Connecting to SQL databases
Most organisations have SQL databases to store large amounts of data such as stocks, sales records, purchasing and customer information. For best performance, organisations typically store these databases on servers located either in the cloud or more commonly on a corporate network. In order to retrieve information from the databases, you must by first connect to the server and then use an SQL querying tool to select the data you would like to view.
A database is made up of a collection of objects. The most common example of a database object is a table. You use tables, which are rows and columns of data, to store the information within a database. Using SQL (Structured Query Language) you can search, retrieve, alter, and delete the information in tables. Some databases are created automatically when you install a database application, but you can create and customise other databases to meet your own requirements.
The main objects in a database are the tables. They are used to store the information contained in the database. A table is a collection of related information organised into rows and columns. Information about each item in the collection is shown as a row (sometimes referred to as a record). Columns contain the same category of information for every item in the table. These columns are sometimes called fields. A table has a header row that which contains the names of the columns and each column stores a specific category of information. For example, a publishing company’s database, might have a table that contains information about the books the company has published. Each row in the table represents a book that the company publishes. The columns in the table store the information the company wants to maintain for each book: the book’s ISBN number, title, production cost, author etc.
Database administrators install database applications such as Oracle MySQL or Microsoft SQL Server on to server computers. A server is provides one or more services to other client computers on a network. Servers are high-performance computers able to provide services to multiple users simultaneously. Normally users do not work on servers directly. Instead, users work with smartphones, tablets or PCs and software applications that access the data stored on the servers. Servers are able to manage resources and provide services to the users that access information held on the server.
A client computer enables users to request the services from a server and display the results returned by the server. The client PC can be located on the same local network as the server or could connect to the server remotely, normally via the Internet. Client computers may run a desktop operating system such as Microsoft Windows.
The client/server architecture is a computer network consisting of a server and one or more clients with processing functions shared between the server and the client. The client/server architecture can be referred to as two-tier architecture. Microsoft SQL Server uses a client/server architecture in which multiple clients connecting to one or more SQL servers to transfer data over the network.
In some companies, network administrators install database servers locally or in data-centres using the organisation’s network. In other companies, network administrators implement SQL databases that are hosted by third-party server providers on the Internet. By choosing to use database servers that are hosted on the Internet they hope to reduce the cost of running and maintaining the servers themselves. Also hosting the servers on the Internet makes them accessible to clients throughout the world.
The “cloud” is a term you will hear frequently to describe the Internet. The term “cloud-based computing” thus denotes the practice of connecting to and using servers that are hosted on the Internet instead of on your local network.
Structured Query Language (SQL) is universal language you use to talk to SQL databases. SQL comprises commands that you can use to fetch, delete, and change information in a database’s tables. SQL consists of three key parts: Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL).
DML commands are used to view, change, and manipulate data within a table. DML includes commands to select, update, and insert data in a table, and delete data from a table. DDL commands allow you to create and define the database and the objects. DDL has commands to create and delete tables. DDL is normally used by database administrators and programmers. The DCL commands are used to control access to the data in a database. It has commands to grant and revoke database privileges. The DCL commands are typically for use by admins and programmers.
Please see further information on SQL training courses which are avialable at all levels.