CLass 11 MySql setup for term 2

 Introduction to Structured Query Language (SQL)

Hello friends in this article we will learn Basic concept of database with SQL and learn about SQL query. We also run how we run sql queries with standard syntax. We have learnt about Relational Database Management System (RDBMS) and purpose in the previous chapter. There are many RDBMS such as MySQL, Microsoft SQL Server, PostgreSQL, Oracle, etc. that allow us to create a database consisting of relations and to link one or more relations for efficient querying to store, retrieve and manipulate data on that database. In this chapter, we will learn how to create, populate and query database using MySQL. Let start with Database concepts with SQL

One has to write application programs to access data in case of a file system. However, for database management systems there are special kind of programming languages called query language that can be used to access data from the database. The Structured Query Language (SQL) is the most popular query language used by major relational database management systems such as MySQL, ORACLE, SQL Server, etc. SQL is easy to learn as the statements comprise of descriptive English words and are not case sensitive. We can create and interact with a database using SQL in an efficient and easy way. The benefit with SQL is that we don’t have to specify how to get the data from the database. Rather, we simply specify what is to be retrieved, and SQL does the rest. Although called a query language, SQL can do much more besides querying. SQL provides statements for defining the structure of the data, manipulating data in the database, declare constraints and retrieve data from the database in various ways, depending on our requirements. In this chapter, we will learn how to create a database using MySQL as the RDBMS software. We will create a database called StudentAttendance  that we had identified in the previous chapter. We will also learn how to populate database with data, manipulate data in that and retrieve data from the database through SQL queries. 

Installing MySQL 

MySQL is an open source RDBMS software which can be easily downloaded from the official website https:// dev.mysql.com/downloads. After installing MySQL, start MySQL service. The appearance of mysql> prompt (Figure 8.1) means that MySQL is ready for us to enter SQL statements. 

Few rules to follow while writing SQL statements in MySQL:

• SQL is case insensitive. That means name and NAME are same for SQL.
• Always end SQL statements with a semicolon (;).
• To enter multiline SQL statements, we don’t write ‘;’ after the first line. We put enter to continue on next line. The prompt mysql> then changes to ‘->’, indicating that statement is continued to the next line. After the last line, put ‘;’ and press enter. 

We know that a database consists of one or more relations and each relation (table) is made up of attributes (column). Each attribute has a data type. We can also specify constraints for each attribute of a relation.

Data type of Attribute

Data type indicates the type of data value that an attribute can have. The data type of an attribute decides the operations that can be performed on the data of that attribute. For example, arithmetic operations can be performed on numeric data but not on character data. Commonly used data types in MySQL are numeric types, date and time types, and string (character and byte) types

Constraints are certain types of restrictions on the data values that an attribute can have. They are used to ensure the accuracy and reliability of data. However, it is not mandatory to define constraint for each attribute of a table. Table 8.2 lists various SQL constraints.

SQL for Data Definition Language SQL provides commands for defining the relation schemas, modifying relation schemas and deleting relations. These are called Data Definition Language (DDL) through which the set of relations are specified, including their schema, data type for each attribute, the constraints as well as the security and access related authorisations. Data definition starts with the create statement. This statement is used to create a database and its tables (relations). Before creating a database, we should be clear about the number of tables in the database, the columns (attributes) in each table along with the data type of each column. This is how we decide the relation schema. 

CREATE Database To create a database, we use the CREATE DATABASE statement as shown in the following syntax: CREATE DATABASE databasename;

To create a database called StudentAttendance, we will type following command at mysql prompt. 

mysql> CREATE DATABASE StudentAttendance; 
Query OK, 1 row affected (0.02 sec) 

Note: In LINUX environment, names for database and tables are case-sensitive whereas in WINDOWS, there is no such differentiation. However, as a good practice, it is suggested to write database or table name in the same letter cases that were used at the time of their creation. A DBMS can manage multiple databases on one computer. Therefore, we need to select the database that we want to use. 

Once the database is selected, we can proceed with creating tables or querying data. Write the following SQL statement for using the database: 

mysql> USE StudentAttendance; 
Database changed Initially, 

the created database is empty. It can be checked by using the Show tables command that lists names of all the tables within a database. 

mysql> SHOW TABLES; 
Empty set (0.06 sec)

CREATE Table 

After creating database StudentAttendance, we need to define relations (create tables) in this database and specify attributes for each relation along with data types for each attribute. This is done using the CREATE TABLE statement. 

Syntax: 

CREATE TABLE tablename( attributename1 datatype constraint, attributename2 datatype constraint, : attributenameN datatype constraint); 

• It is important to observe the following points with respect to the Create Table statement: 
• N is the degree of the relation, means there are N columns in the table. 
• Attribute name specifies the name of the column in the table. 
• Datatype specifies the type of data that an attribute can hold.
• Constraint indicates the restrictions imposed on the values of an attribute. By default, each attribute can take NULL values except for the primary key.

Create table STUDENT. 

mysql> CREATE TABLE STUDENT( 
 -> RollNumber INT, 
 -> SName VARCHAR(20), 
 -> SDateofBirth DATE, 
 -> GUID CHAR(12), 
 -> PRIMARY KEY (RollNumber)); 
Query OK, 0 rows affected (0.91 sec)

DESCRIBE Table 

We can view the structure of an already created table using the describe statement. 

Syntax: DESCRIBE tablename; MySQL also supports the short form DESC of DESCRIBE to get description of table. To retrieve details about the structure of relation STUDENT, we can write DESC or DESCRIBE followed by table name:

The show table command will now return the table STUDENT: 

mysql> SHOW TABLES;

After creating a table we may realize that we need to add/remove an attribute or to modify the datatype of an existing attribute or to add constraint in attribute. In all such cases, we need to change or alter the structure of the table by using the alter statement.

Add primary key to a relation 

The below MySQL statement adds a primary key to the GUARDIAN relation: 

mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID); 

Query OK, 

0 rows affected (1.14 sec) 

Records: 0 Duplicates: 0 Warnings: 0 

Now let us add primary key to the ATTENDANCE relation. The primary key of this relation is a composite key made up of two attributes — AttendanceDate and RollNumber. 

mysql> ALTER TABLE ATTENDANCE

 -> ADD PRIMARY KEY(AttendanceDate,

 -> RollNumber); 

Query OK, 0 rows affected (0.52 sec) 

Records: 0 Duplicates: 0 Warnings: 0

DROP Statement 

Sometimes a table in a database or the database itself needs to be removed. We can use DROP statement to remove a database or a table permanently from the system. However, one should be very cautious while using this statement as it cannot be undone. Syntax to drop a table: DROP TABLE table_name; 

Syntax to drop a database: 

DROP DATABASE database_name; 

Cautions:

• Using the Drop statement to remove a database will ultimately remove all the tables within it. 
• DROP statement will remove the tables or database created by you. Hence you may apply DROP statement at the end of the chapter.

SQL for Data Manipulation 

In the previous section, we created the database StudentAttendance having three relations STUDENT, GUARDIAN and ATTENDANCE. When we create a table, only its structure is created but the table has no data. To populate records in the table, INSERT statement is used. Similarly, table records can be deleted or updated using SQL data manipulation statements.  

INSERTION of Records 

INSERT INTO statement is used to insert new records in a table. Its syntax is:

The below statement inserts the first record in the table. 

mysql> INSERT INTO GUARDIAN 

 -> VALUES (444444444444, 'Amit Ahuja', 

 -> 5711492685, 'G-35,Ashok vihar, Delhi' ); 

Query OK, 1 row affected (0.01 sec) 

We can use the SQL statement SELECT * from table_ name to view the inserted records. The SELECT statement will be explained in next section.

mysql> INSERT INTO STUDENT (RollNumber, SName, 

 -> SDateofBirth, GUID) 
 -> VALUES (1,'Atharv Ahuja','2003-05-15', 
 -> 444444444444); 
Query OK, 1 row affected (0.02 sec)

Continue.....

important search queries

database concepts pdf class 11 database concepts pdf class 12 database concepts class 12 notes what is database class 11 introduction to database concepts pdf dbms class 11 notes database concepts class 11 ncert solutions mcq on database concepts class 11,database concepts pdf class 11 dbms class 11 notes mysql notes for class 11 pdf sql class 11 notes sql class 12 notes pdf database concepts class 11 ncert solutions what is database class 11 questions on database concepts class 11