{"id":12739,"date":"2023-04-13T12:56:22","date_gmt":"2023-04-13T07:26:22","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=12739"},"modified":"2023-04-25T13:13:31","modified_gmt":"2023-04-25T07:43:31","slug":"sql-database-fundamentals","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/sql-database-fundamentals\/","title":{"rendered":"SQL Database Fundamentals"},"content":{"rendered":"\n

SQL Database Fundamentals<\/strong><\/h2>\n\n\n\n

Structured Query language is one of the basic building blocks of the advanced database architecture. SQL <\/a>defines the methods that are used to create and manipulate relational databases all the major platforms. It can be called as Sequel. It comes in many flavours. Oracle databases use their proprietary PL\/SQL, Microsoft SQL server that makes to Transact SQL.<\/p>\n\n\n\n

DDL and DML<\/strong><\/h2>\n\n\n\n

<\/strong>SQL commands that will be categorised into two main sub languages. The Data Definition Language that contains the commands used to create and destroy databases and database objects. After the database structure which is defined with DDL database administrators and users will be used to insert, retrieve and manipulate or modify the data that will be contained. SQL also assists the Data control language. DCL will governs the security that access the objects within the Database.<\/p>\n\n\n\n

Data definition language (DDL) commands:<\/strong><\/h2>\n\n\n\n

<\/strong>The Data definition language is used to create and destroy databases and database objects. These commands are primarily used by database administrators during the setup and setup and removal phases of database project. DDL revolves around four primary commands-create, use, alter and Drop. <\/p>\n\n\n\n

Create<\/strong><\/h2>\n\n\n\n

This create command will establishe the databases, tables or queries on your platform. For example the below command:<\/p>\n\n\n\n

create database employees1;<\/p>\n\n\n\n

This will create the database employees1. After we create another variant database, the next step is to create tables which contains data and accomplishes the task<\/p>\n\n\n\n

Create Table personal info 11(first name char(20), last_name char(20,employee_id11 int  ); <\/p>\n\n\n\n

-establishes a titled personal_info11 in current database.<\/p>\n\n\n\n

Use<\/strong><\/h2>\n\n\n\n

The USE<\/strong> command will show the active database. For example, if we are presently working in the sales database and want to issue some commands which will affect the employee database, preface them with the below sql command:<\/p>\n\n\n\n

Use employees<\/strong><\/h2>\n\n\n\n

Double check the database, if we are working modify its definition through the alter command which changes to structure of a table without deleting and recreating  it. Take a look at the following command<\/p>\n\n\n\n

ALTER TABLE personal_info 11 ADD salary1 money null;<\/h2>\n\n\n\n

This is an example that adds a new attribute to the \u2018personal_info\u2019 table employee\u2019s salary. The money argument that says that an employee\u2019s salary stores by using a dollars and cents format. The null keyword tells the database that is ok for the field that contain no value that is given employee salary.<\/p>\n\n\n\n

DROP<\/strong><\/h2>\n\n\n\n

The final command of the data definition language- DROP<\/strong> will remove the whole database objects from our DBMS. For example to permanently remove the personal_info table that we created use following command.<\/p>\n\n\n\n

DROP TABLE personal_info11;<\/h2>\n\n\n\n

2. Data Manipulation Language(DML) commands<\/strong><\/p>\n\n\n\n

The Data Manipulation Language(DML) is used to retrieve, insert and modify database information. The DML commands offer the typical framework for interacting within the database on a routine basis.<\/p>\n\n\n\n

Insert command<\/strong><\/h2>\n\n\n\n

<\/strong>The insert<\/strong> command will add records to an existing table. Returning to the  personal info example from the previous section imagine that our HR department needs to add a new employee to its database.<\/p>\n\n\n\n

INSERT INTO personal_info11
values(‘bart’,’simpson’,12345,$45000);<\/p>\n\n\n\n

Select<\/strong><\/h2>\n\n\n\n

<\/strong>This select<\/strong> command is the commonly used command. It retrieves main information from any sql operational database. This command shown below retrieves all the information with the personal_info table like <\/p>\n\n\n\n

Select * from personal_info11;<\/p>\n\n\n\n

It limits the attributes which are retrieved from the database by knowing what will be selected. For example, the human resources department will require a list of the last names of all the employees in the company. Like<\/p>\n\n\n\n

SELECT last_name
FROM personal_info;<\/p>\n\n\n\n

Here the where command will be having a limit the records that are retrieved to those that will be meet specified criteria.The CEO will be interested while in reviewing all the personnel records of all the highly paid employees like <\/p>\n\n\n\n

SELECT *
FROM personal_info11
WHERE salary1 > $50000;<\/p>\n\n\n\n

Update<\/strong><\/h2>\n\n\n\n

<\/strong>The update<\/strong> command modifies the information will contain within the table by bulk or individually. Assume the company which gives all employees a 3 percent cost of living that increase in their salary annually.<\/p>\n\n\n\n

UPDATE personal_info11
SET salary1 = salary 1* 1.03;<\/p>\n\n\n\n

Questions<\/strong><\/h2>\n\n\n\n
    \n
  1. What is SQL?<\/li>\n\n\n\n
  2. What are DDL commands?<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    SQL Database Fundamentals Structured Query language is one of the basic building blocks of the advanced database architecture. SQL defines the methods that are used to create and manipulate relational databases all the major platforms. It can be called as Sequel. It comes in many flavours. Oracle databases use their proprietary PL\/SQL, Microsoft SQL server […]<\/p>\n","protected":false},"author":1,"featured_media":12740,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-12739","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qa-tutorials"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/12739","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=12739"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/12739\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/12740"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=12739"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=12739"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=12739"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}