PL-MYSQL BASIC

 

WHAT IS DATABASE PROGRAMMING?
Software that is used to manage data and information structured as fields, records and files.
A database program is the heart of a business information system and provides file creation, data entry, update, query and reporting functions.

DATABASE LANGUAGE
A Database language has Data Definition Language(DDL), which is used to construct a database.
Data Manipulation Language (DML), which is used to access a database.
DDL implements database schema at the physical, logical and external level.
DML provides the statements to retrieve, modify, insert and delete the data from the database.

IS SQL A DATABASE?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

All the Relational Database Management Systems(RDBMS) like MySQL, MS Access, Oracle, and SQL server use SQL as their standard database language.

SQL

Structured Query Language (SQL) is a domain-specific language used to manage relational databases and manipulate the data stored inside them.
Developers use SQL statements to perform tasks in SQL Server, MySQL, Oracle, Access, MatiaDB and PostgreSQL and other popular databases.

SQL LANGUAGE
DML:
Select, Insert, Update, Delete
DDL:
Create, Alter, Drop
DCL:
Grant, Revoke
TCL:
Begin, Tran, Commit, Rollback

DDL
A DDL is a language used to define data structures and modify data.
DDL or Data Definition Language actually consists of SQL commands that can be used to define the database schema.
It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.
For example, DDL commands can be used to add, remove, or modify tables with in a database.
EXAMPLES

CREATE
is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
DROP
is used to delete objects from the database.
ALTER
is used to alter the structure of the database.
TRUNCATE
is used to remove all records from a table, including all spaces allocated for the records are removed.
COMMENT
is used to add comments to the data dictionary.
RENAME
is used to rename an object existing in the database.

DML
A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
A popular data manipulation language is that of Structured Query Language(SQL), which is used to retrieve and manipulate data in a relational database.
EXAMPLES

INSERT
is used to insert data into a table.
UPDATE
is used to update existing data within a table
DELETE
is used to delete records from a database table
SELECT
this command reads and pulls out the records from the database.



There are two types of DML

PROCEDURAL
in which the user specifies what data is needed and how to get it.
NON-PROCEDURAL
in which the user specifies only what data is needed.

DCL

EXAMPLES

GRANT
gives user's access privileges to database.
REVOKE
withdraw user's access privileges given by using the GRANT command.

BASIC STRUCTURE OF SQL

The basic structure of an SQL expression consists of three clauses:

The select clause which corresponds to the projection operation. It is the list of attributes that will appear in the resulting table.
The from clause which corresponds to the Cartesian-product operation. It is the list of tables that will be joined in the resulting table.
The where clause which corresponds to the selection operation. It is the expression that controls which rows appear in the resulting table.

AGGREGATE FUNCTIONS

An aggregate is a collection of people who happen to be at the same place at the same time but who have no other connection to one another.
EXAMPLE:

The people gathered in a restaurant on a particular evening are an example of an aggregate, not a group.

AGGREGATE FUNCTION IS SQL

COUNT counts how many rows are in a particular column.

SUM adds together all the values in a particular column.

MIN and MAX return the lowest and highest values in a particular column, respectively.

AVG calculates the average of a group of selected values.
NULL VALUES

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is different than a zero value or a field that contains space.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
A Null value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

https://www.shoutcoders.com/pl-sql-environment-setup/
Check this

Comments

Post a Comment

Popular posts from this blog

Oracle Live SQL

Image
  In this article, you are going to learn how to use Oracle Live SQL and how to work with Oracle Database without installing the database. Get instant access to the Oracle Database and learn from a collection of community scripts and tutorials. Write your own SQL scripts and easily share them with others. All you need to get started is your Oracle.com account. Following steps helps you to build your knowledge in SQL. Step 1: Start with a basic structure as given in the following below diagram. Fig: Table Step 2: Learn some basic terminologies i.e. Field, Row, And Column (s). So, it helps you to better understanding. Step 3: Break it into some simple steps, i.e. Create a Table, Make some conditions on attributes (integrity constraints), Inserting values, and Make sure all values are inputted in their proper place. Step 4: There are some queries and you should answer those. Step 5: Commit; After Creating and inserting values because sometimes data is not save
Read more

PL/SQL XAMPP EXAMPLE SET 2

  In this , we are going to learn how to use SQL in XAMPP. Here mainly cover CREATE, FOREIGN KEY ADD, ALTER, INSERT, SELECT, AGE RETRIEVE, TIMESTAMPDIFF, DISTINCT, LIKE, CURDATE, COUNT, SUM, MAX, AND, OR, IN, NOT IN, TRUNC, RENAME OPERATION ,UNION, BETWEEN, NESTED QUERIES. All types are discussed in the given below links, you might like, Preparation Related Notes: https://www.shoutcoders.com/sql-preparation-time-note-4/ , https://www.shoutcoders.com/sql-preparation-time-note-3/ , https://www.shoutcoders.com/sql-preparation-time-note-2/ , https://www.shoutcoders.com/sql-preparation-time-note-1/ PL/SQL XAMPP EXAMPLE SET 2: Create the following table (i) Studies ( P_name, S_place, Course, Cost_no ) create table studies ( P_name varchar(20) primary key, Study_place varchar(20), Course varchar(20), Cost_no int(20)); OUTPUT: Table Created (ii) Programmer ( P_name, DOB, DOJ, Gender, Prof1, Prof2, Sal ). Make P_name as foreign key from Studies relation. create
Read more

PL/SQL XAMPP EXAMPLE SET 1

Image
  In this PL/SQL XAMPP EXAMPLE SET 1, You can to learn how to use SQL commands in XAMPP. Here cover DML, Data Insertion, Data Updation, Data Deletion, Data Selection, CREATE, DESCRIBE SCHEMA, INSERT, ADD COLUMN, REMOVE COLUMN, UPDATE, ALTER, PRIMARY KEY, MODIFY, DROP COLUMN, DATATYPE CHANGE, RENAME FIELD, DISTINCT, DELETE RECORD, CLEAR ALL DATA, REMOVE TABLE. There are some preparation notes, links are given below, you might like, Preparation Notes: https://www.shoutcoders.com/sql-preparation-time-note-4/ , https://www.shoutcoders.com/sql-preparation-time-note-3/ , https://www.shoutcoders.com/sql-preparation-time-note-2/ , https://www.shoutcoders.com/sql-preparation-time-note-1/ Oracle Database PL/SQL XAMPP EXAMPLE SET 1 : 1. Create the following table Student {roll, name, address, ph_no, DOB, MA, HS, DOA, Stream, C_num} create table student (Roll int(4), Name varchar(10), Address varchar(20), Ph_num int(10), DOB date, MA int(4), HS int(4), DOA date, Stream var
Read more