Module - II

Database and MySQL

Q1) Introduction to database? 5 Marks

A1) Database

A database is a collection of data that has been organized in a systematic manner. They allow for electronic data storage and manipulation. Data handling is simplified thanks to databases.

Let's look at an example of a database: an online telephone directory uses one to store information about persons, phone numbers, and other contact information. Your power company employs a database to keep track of invoices, client issues, and defect data, among other things.

Fig 1: database

Types of Databases

Depending upon the usage requirements, there are following types of databases available in the market:

●       Centralized database

●       Distributed database

●       Personal database

●       End-user database

●       Commercial database

●       NoSQL database

●       Operational database

●       Relational database

●       Cloud database

●       Object-oriented database

●       Graph database

Example

The following are some of the most well-known databases:

1. Oracle Database
2. Sybase
3. MySQL

Q2) Write the advantages and disadvantages of using a database? 5 Marks

A2) Advantages of using Database

Databases provide numerous advantages -

●      Data redundancy has been reduced.

●      Update mistakes were reduced, and consistency was improved.

●      Data integrity is improved, as is independence from application programs.

●      Users have better data access thanks to the use of host and query languages.

●      Data security has been improved.

●      Costs of data entry, storage, and retrieval are reduced.

Disadvantages of using Database

Databases have a number of drawbacks

●      Databases have some drawbacks, despite the fact that they allow businesses to store and access data easily.

●      Cost

●      Security

●      Complexity

Q3) Write about relational database systems? 5 Marks

A3) SQL stands for Structured Query Language, and it refers to a method for arranging data in the form of tables, columns, and rows. In a SQL database, how is data organized? The table itself would be made up of only one variable or object, which we would examine. The row is a record of the data points per column, whereas the column is the data point that needs to be recorded.

For example, if you wanted to sort data on the weather at a specific time of day on a specific day, it would be formatted as follows:

●       Table: Weather

●       Columns: Days of the Week

●       Rows: Time of Day

●       Data Points: Degrees Fahrenheit

All queries would be tied to this table in this way, and the table's structure would allow for easy sorting, filtering, computations, and so on. If we ever need to connect two tables, for example, if we want to know what the weather was like at a given moment and how that relates to a baseball game's expected result, we create a key. This key enables links to be created between two or more tables in order to solidify linkages.

Advantages

●      The data can be simply organized into categories.

●      Your data is well-organized, has a clear meaning, and is simple to navigate.

●      Between data points, relationships can be simply defined.

Q4) What are non relational database systems? 5 Marks

A4) Non - Relational database system

A NoSQL database, as contrast to a relational database, is less structured/constrained in format, allowing for greater flexibility and adaptation. If you're working with a dataset that isn't clearly defined, that is, it isn't organized or structured, you won't be able to create defined tables and relationships between the data.

Because the information being acquired isn't organized enough to be split into tables and identify relationships between them, Facebook Messenger, for example, uses a NoSQL database. A non-relational database is required to store a large amount of unstructured data. Consider the data as being stored in a single huge word document. Everything is in place. The document grows in length as additional information is entered. You must essentially ‘control/command + F' and search for the data itself if you want to find and pull data.

Advantages

●      Data is not contained within a defined group.

●      You can use functions that provide you more freedom.

●      You may make your data and analysis more dynamic by allowing for more different inputs.

Q5) Explain View of data? 8 Marks

A5) A view is nothing more than a SQL statement with a name that is stored in the database. A view is a preset SQL query that combines the contents of a table.

A view can contain all of a table's rows or only a subset of them.

A view can be built from a single table or multiple tables, depending on the SQL query used to build the view.

Users can accomplish the following with views, which are a type of virtual table

●      Structure data in a way that feels natural or intuitive to users or groups of users.

●      Restrict data access so that a user can only see and (sometimes) edit what they need.

●      Summarize data from several tables so that a report can be generated.

Creating a view

The Construct VIEW statement is used to create database views. A single table, many tables, or another view can all be used to generate views.

A user must have the required system privilege according to the specific implementation to create a view.

The basic syntax for CREATE VIEW is as follows:

CREATE VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

Multiple tables can be included in your SELECT statement in the same manner they are in a standard SQL SELECT query.

Inserting row into a view

A view can have rows of data placed into it. The same rules apply to the INSERT command as they do to the UPDATE command.

We can't insert rows in the CUSTOMERS VIEW because it doesn't have all of the NOT NULL fields; otherwise, you can insert rows in a view in the same manner you can in a table.

Deleting row into a view

A view's rows of data can be erased. The DELETE command follows the same principles as the UPDATE and INSERT commands.

Dropping view

Obviously, where there is a view, there must be a means to remove it if it is no longer required. The syntax is quite straightforward and is shown below.

DROP VIEW view_name;

The example below shows how to remove the CUSTOMERS VIEW from the CUSTOMERS table.

DROP VIEW CUSTOMERS_VIEW;

Q6) Describe Schema statements? 5 Marks

A6) It is the database's logical framework for storing data. A schema, like a database, is a collection of tables containing rows and fields for which a distinct query can be made. In MySQL, schema refers to a template. They provide the size, kind, and grouping of data.

Database objects like views, tables, and privileges are included in the schemas. Data types, functions, and operators are all part of a schema. They're used in business analysis to figure out what features to look for and how to incorporate them into new data sets using relational databases and information schemas.

MySQL uses schemas to define the database structure by combining rows and values from the same table with the appropriate data types. They employ indexes to search the entire table for relevant rows. Because solid logical and physical design is the cornerstone for running high-performance queries, it is vital to develop schemas for specific queries.

The schema objects, which contain a trigger, check constraint, and foreign key, play an important role in schemas. Databases, tables, schema objects owners, and mapping MySQL to other databases are all covered by schema migrations.

Creation

The process of creating a schema is similar to that of creating a database.

Create a schema name;

The following command will display a list of available schemas:

My SQL Show schemas;

MySQL Database Table:

MySQL desc table name;

Another query to see the schema of a table in a database:

Mysql use database name; (to work in the database)

MySQL uses schema table name;

Q7) Describe names (table & column names)? 8 Marks

A7) In MySQL, you can get the names of all tables by using the "display" keyword or queryING INFORMATION SCHEMA.

To acquire all table names for a database in SQL Server, you can use either sys.tables or INFORMATION_SCHEMA.

Mysql> SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';

+------------+

| TABLE_NAME |

+------------+

| department |

| employee   |

| role       |

| user       |

+------------+

4 rows in set (0.00 sec)

Mysql> SHOW tables;

+----------------+

| Tables_in_test |

+----------------+

| department     |

| employee       |

Column name

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}

{FROM | IN} tbl_name

[{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

SHOW COLUMNS is a command that displays information about the columns in a table. It can also be used to generate views. SHOW COLUMNS only shows information for columns for which you have permission.

Mysql> SHOW COLUMNS FROM City;

+-------------+----------+------+-----+---------+----------------+

| Field       | Type     | Null | Key | Default | Extra          |

+-------------+----------+------+-----+---------+----------------+

| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |

| Name        | char(35) | NO   |     |         |                |

| CountryCode | char(3)  | NO   | MUL |         |                |

| District    | char(20) | NO   |     |         |                |

| Population  | int(11)  | NO   |     | 0       |                |

+-------------+----------+------+-----+---------+----------------+

Tbl name is an alternative to tbl name. The syntax for db name is db name. Tbl name. These two statements are interchangeable:

SHOW COLUMNS FROM mytable FROM mydb;

SHOW COLUMNS FROM mydb.mytable;

The optional EXTENDED keyword causes the output to contain information about MySQL's internal secret columns, which aren't visible to users.

The FULL keyword adds the column collation and comments, as well as the privileges you have for each column, to the output.

Q8) Define Data types? 5 Marks

A8) A Data Type defines a certain type of data, such as integers, floating points, Booleans, and so on. It also describes the type's available values, actions that can be performed on the type, and how the type's values are stored. Each database table in MySQL has a number of columns, each with its own data type.

MySQL has a large number of SQL standard data types in many different categories. It employs a variety of data types, which can be divided into the following categories:

●       Numeric

●       Date and Time

●       String Types

Char - A fixed-length string (for example, CHAR(5)) between 1 and 255 characters in length, right-padded with spaces to the specified length when stored. It is not necessary to specify a length, however the default is 1.

Varchar - A variable-length string with a length of 1 to 255 characters. VARCHAR, for example (25). When creating a VARCHAR field, you must specify a length.

Text (BLOB) - A field that can hold up to 65535 characters. "Binary Enormous Objects," or BLOBs, are used to store large volumes of binary data, such as photographs or other types of files. TEXT fields can also carry a lot of information. The distinction between the two is that on BLOBs, the sorts and comparisons on the stored data are case sensitive, whereas in TEXT fields, they are not. With BLOB and TEXT, you don't have to provide a length.

Mediumtext - The maximum length of a BLOB or TEXT column is 16777215 characters. With MEDIUMBLOB or MEDIUMTEXT, you don't have to specify a length.

Longtext - The maximum length of a BLOB or TEXT column is 4294967295 characters. With LONGBLOB and LONGTEXT, you don't have to define a length.

Smallint - This is a tiny integer that can be either signed or unsigned. The allowed range, if signed, is -32768 to 32767. If the value is unsigned, the range is 0 to 65535. A width of up to 5 digits can be specified.

Bigint - The size of a huge integer, which can be either signed or unsigned. The range allowed if signed is -9223372036854775808 to 9223372036854775807. If the value is unsigned, the range is 0 to 18446744073709551615. A width of up to 20 digits can be specified.

Q9) Explain boolean, decimal, float, double, date, datetime, timestamp, year and time? 5 Marks

A9) Boolean - It is used only for the true and false condition. It considered numeric value 1 as true and 0 as false.

Decimal - A signed floating-point number that has been unpacked. Each decimal in the unpacked decimals corresponds to one byte. It is necessary to specify the display length (M) and the number of decimals (D). DECIMAL is a synonym for NUMERIC.

Float (m,d) - This is a signed floating-point number that can't be unsigned. The display length (M) and the number of decimals can both be customized (D). This is optional; the default value is 10,2, where 2 denotes the number of decimals and 10 denotes the total number of digits (including decimals). For a FLOAT, decimal precision can go up to 24 digits.

Double (m,d) - A floating-point number with double precision that cannot be unsigned. The display length (M) and the number of decimals can both be customized (D). This is optional; the default value is 16,4, where 4 is the number of decimals. For a DOUBLE, decimal precision can go up to 53 digits. DOUBLE is a synonym for REAL.

Date - A date between 1000-01-01 and 9999-12-31 in the YYYY-MM-DD format. The date July 19th, 2021, for example, would be kept as 2021-7-19.

Datetime - A date and time combination between 1000-01-01 00:00:00 and 9999-12-31 23:59:59 in the YYYY-MM-DD HH:MM:SS format. For example, on July 19th, 2021, 3:30 p.m. Would be kept as 1973-12-30 15:30:00.

Timestamp - A date between midnight on January 1, 1970 and the year 2037. This format appears to be similar to the previous DATETIME format, but without the hyphens between the digits; for example, 3:30 p.m. On December 30th, 1973 would be kept as 19731230153000. ( YYYYMMDDHHMMSS ).

Year - A year is stored in either a 2-digit or 4-digit format. YEAR can be any year between 1970 and 2069 provided the length is specified as 2 (for example, YEAR(2)) (70 to 69). YEAR can range from 1901 to 2155 if the length is given as 4. The default length is four characters.

Time - Stores the time in a HH:MM:SS format.

Q10) Explain Creating Database? 5 Marks

A10) A database is used to organize and store a collection of records. It lets us organize data into tables, rows, columns, and indexes so that we can quickly access the information we need. We can quickly access and manage the records using the database.

Using the CREATE DATABASE statement with the following syntax, we may create a new database in MySQL:

CREATE DATABASE [IF NOT EXISTS] database_name 

[CHARACTER SET charset_name] 

[COLLATE collation_name]; 

The following are the parameter descriptions for the above syntax:

Database_name : It's the name of a new database in the MySQL server instance that should be unique. When we create a database that already exists, the IF NOT EXIST clause prevents an error.

Charset_name : It's a choice. It's the name of the character set that's used to hold all of the characters in a string. The MySQL database server can handle a wide range of character sets. MySQL uses the default character set if we don't specify it in the statement.

Collation_name : It compares characters from a certain character set and is optional.

Q11) How to inserting data? 8 marks

A11) You'll need to use the SQL INSERT INTO command to insert data into a MySQL table.

Syntax -

The following is a generic SQL syntax for inserting data into a MySQL table using the INSERT INTO command.

INSERT INTO table_name ( field1, field2,...fieldN )

VALUES

( value1, value2,...valueN );

To insert string data types, all values must be enclosed in double or single quotes. Consider the word "value."

Use the following statement if we wish to insert numerous records in a single command:

INSERT INTO table_name VALUES 

( value1, value2,...valueN ) 

( value1, value2,...valueN ) 

........... 

( value1, value2,...valueN ); 

In the value fields of the above grammar, all rows should be separated by commas.

Example

With the help of several examples, let's learn how INSERT statements in MySQL function. To begin, use the following command to create a table called "People" in the database:

CREATE TABLE People( 

Id int NOT NULL AUTO_INCREMENT, 

Name varchar(45) NOT NULL, 

Occupation varchar(35) NOT NULL, 

Age int, 

PRIMARY KEY (id) 

); 

If we wish to save records for all fields, we can use the following syntax:

INSERT INTO People (id, name, occupation, age)  

VALUES (101, 'Peter', 'Engineer', 32);

The following partial field statements are used to store records without providing all fields. In such a circumstance, field names must be specified.

INSERT INTO People (name, occupation)  

VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor'); 

Q12) Define Updating data? 5 Marks

A12) The UPDATE query in MySQL is a DML statement that modifies the data in a MySQL table in the database. In a real-world situation, records are updated throughout time. As a result, we'll need to alter the table values as well. This necessitates the usage of the UPDATE query.

The SET and WHERE clauses are used with the UPDATE statement. To update the values of a particular column, use the SET clause. At the same moment, we can change a single or numerous columns.

Syntax

A generic syntax for the UPDATE command to edit data in a MySQL table is as follows:

UPDATE table_name    

SET column_name1 = new-value1,  

Column_name2=new-value2, ...   

[WHERE Clause] 

The following is a list of parameters used in the UPDATE statement's syntax:

Table_name -nIt's the name of a table where we'd like to make a change.

Column_name - It's the name of a column in which we wish to use the SET clause to update with a new value. If you need to change multiple columns, use the comma operator to separate them and specify the value in each column.

WHERE clause - It's a choice. It is used to give the name of the row in which we will make the update. If we don't include this clause, MySQL will update all of the records.

In MySQL, the UPDATE command accepts the following modifiers:

LOW_PRIORITY: This modification tells the statement to hold off on executing the UPDATE command until no other clients are reading from the table. It only has an effect on storage engines that exclusively use table-level locking.

IGNORE: This modification permits the statement to continue running even if it encounters mistakes. The rows are not changed if duplicate-key conflicts are discovered.

As a result, the whole syntax of the UPDATE statement is as follows:

UPDATE [LOW_PRIORITY] [IGNORE] table_name 

SET column_assignment_list 

[WHERE condition] 

Q13) Write Deleting data? 5 Marks

A13) The DELETE statement in MySQL is used to delete records from a table that are no longer needed in the database. This MySQL query deletes an entire row from the table and returns the number of deleted rows. It also allows us to delete multiple entries from a table in a single query, which is useful when eliminating big amounts of data from a table. We can also erase data based on conditions by using the delete statement.

We won't be able to recover the records we deleted with this query. As a result, before deleting any records from the table, you should make a database backup. We can restore data from database backups anytime we need it in the future.

Syntax

The syntax that demonstrates how to utilize the DELETE command is as follows:

DELETE FROM table_name WHERE condition; 

We must first identify the table name from which we wish to delete data in the aforementioned sentence. Second, in the WHERE clause, we must give the criteria to delete records, which is optional. This query will remove whole records from the database table if the WHERE clause is not included in the statement.

We must use the JOIN clause with the DELETE statement if we want to delete records from numerous tables using a single DELETE query.

If we wish to delete all records from a database without knowing how many rows were destroyed, we should use the TRUNCATE TABLE statement, which is faster.

Q14) What is expressions? 12 Marks

A14) In a programming language, an expression is a collection of values, variables, operators, and functions that are interpreted (evaluated) according to the precedence and association rules for that programming language, which computes and then returns another value (in a stateful environment). It is said that the expression evaluates to that value.

Literal values

A literal value is a type of constant. Strings, numbers, hexadecimal values, boolean values, and NULL are all examples of literal values.

Mysql> SELECT 3, 'Wolf', 34.5, 0x34, 0+b'10111';

+---+------+------+------+------------+

| 3 | Wolf | 34.5 | 0x34 | 0+b'10111' |

+---+------+------+------+------------+

| 3 | Wolf | 34.5 | 4    |         23 |

+---+------+------+------+------------+

We return five literals in this case. An integer, a text, a floating point number, a hexadecimal number, and a binary value are all examples of data types. The hexadecimal value is preceded by the 0x character, which is a computer language standard. A b character precedes the binary value, which is put inside single quotations. We append a zero to the binary notation to display a readable value.

Mysql> SELECT NULL, \N;

+------+------+

| NULL | NULL |

+------+------+

| NULL | NULL |

+------+------+

This is the value NULL. It's the lack of value. N is a synonym for NULL.

Mysql> SELECT TRUE, FALSE;

+------+-------+

| TRUE | FALSE |

+------+-------+

|    1 |     0 |

+------+-------+

MySQL also understands TRUE and FALSE boolean values. They can be written in any letter case that you like.

Variables

A variable is a value that has a symbolic name. The value of this variable may change over time. The @ character precedes variables in MySQL.

Mysql> SET @name = 'Jane';

Mysql> SELECT @name;

+-------+

| @name |

+-------+

| Jane  |

+-------+

Operator

Expressions are built using operators. Mathematical operators and SQL operators are quite similar. Operators are divided into two categories. There are two types of binary and unary. Unary operators work with one operand, while binary operators work with two. One or two operands can be used with an operator. An operand is one of an operator's inputs (arguments).

There are various different sorts of operators:

●       Arithmetic operators

●       Boolean operators

●       Relational operators

●       Bitwise operators

●       Other operators

Q15) Explain lower, upper, reverse, length, ltrim, rtrim, trim? 12 Marks

A15) lower

The LOWER function in MySQL turns all characters in a string to lowercase. If there are any characters in the string that are not letters, this function has no effect on them.

The LOWER function in MySQL has the following syntax:

LOWER( string )

String - The string that has to be converted to lowercase.

Example

Let's look at some examples of the MySQL LOWER function and see how to use it in MySQL.

Mysql> SELECT LOWER('Tech on the Net');

Result: 'tech on the net'

Mysql> SELECT LOWER('MY SQL');

Result: 'my sql'

Upper

The UPPER function in MySQL turns all characters in a string to uppercase. If there are any characters in the string that are not letters, this function has no effect on them.

The UPPER function in MySQL has the following syntax:

UPPER( string )

String - The string that has to be converted to uppercase.

Example

Let's look at several examples of the MySQL UPPER function and see how to use it in MySQL.

Mysql> SELECT UPPER('my sql');

Result: 'MY SQL'

Mysql> SELECT UPPER('computer science');

Result: 'COMPUTER SCIENCE’

Reverse

The REVERSE function in MySQL returns a string that has the characters arranged in reverse order.

The REVERSE function in MySQL has the following syntax:

REVERSE( string )

String - The inverted characters from the source string.

Example

Let's have a look at some MySQL REVERSE function examples and see how to use the function in MySQL.

Mysql> SELECT REVERSE('abcde');

Result: 'edcba'

Mysql> SELECT REVERSE('123');

Result: '321'

Mysql> SELECT REVERSE(123);

Result: '321'

Length

The LENGTH function in MySQL returns the length of a string (measured in bytes).

The LENGTH function in MySQL has the following syntax:

LENGTH( string )

String - The length of the string to return.

A multi-byte character is counted as more than one byte when using the LENGTH function.

Example

Let's look at several LENGTH function examples in MySQL and see how to use it.

Mysql> SELECT LENGTH(NULL);

Result: NULL

Mysql> SELECT LENGTH('');

Result: 0

Mysql> SELECT LENGTH(' ');

Result: 1

Mysql> SELECT LENGTH('Tech on the Net');

Result: 15

Mysql> SELECT LENGTH('google.com');

Result: 10

Ltrim

The LTRIM function in MySQL eliminates all space characters from a string's left side.

The LTRIM function in MySQL has the following syntax:

LTRIM( string )

String - Trim the space characters off the left-hand side with this string.

Example

Let's look at several LTRIM function examples in MySQL and see how to use it.

Mysql> SELECT LTRIM('   Tech on the net');

Result: 'Tech on the net'

Mysql> SELECT LTRIM('   Tech   on   the   net   ');

Result: 'Tech   on   the   net   '

Mysql> SELECT LTRIM('    google.com');

Result: 'google.com'

Q16) Write about trim, left, right, mid, concat and now? 12 Marks

A16) Trim

The TRIM function in MySQL removes all specific characters from the start or end of a string.

The TRIM function in MySQL has the following syntax:

TRIM( [ LEADING | TRAILING | BOTH ] [ trim_character FROM ] string )

LEADING - Optional. The trim character is removed from the beginning of the string.

TRAILING - Optional. The trim character is removed from the end of the string.

BOTH - Optional. The trim character is removed from the beginning and end of the string.

Trim_character - Optional. The character from the string that will be eliminated. If this argument is not specified, space characters will be removed from the string.

String - Trimming the string

Example

Let's have a look at some MySQL TRIM function examples and see how to apply it in MySQL.

Mysql> SELECT TRIM(LEADING '0' FROM '000123');

Result: '123'

Mysql> SELECT TRIM(TRAILING '1' FROM 'Tech1');

Result: 'Tech'

Mysql> SELECT TRIM(BOTH '123' FROM '123Tech123');

Result: 'Tech'

Left

The MySQL LEFT function extracts a substring from a string beginning with the left-most character.

The LEFT function in MySQL has the following syntax:

LEFT( string, number_of_characters )

String - The string from which you want to extract data.

Number_of_character - Starting with the left-most character, the number of characters you want to extract from the string.

Example:

Let's have a look at some examples of the LEFT function in MySQL and see how to use it.

Mysql> SELECT LEFT('Teching', 1);

Result: 'T'

Mysql> SELECT LEFT('google.com', 100);

Result: 'google.com'

Right

The MySQL RIGHT function extracts a substring from a string beginning with the right-most character.

The RIGHT function in MySQL has the following syntax:

RIGHT( string, number_of_characters )

String - The string from which you want to extract data.

Number_of_characters - Starting with the right-most character, the number of characters you want to extract from the string.

Example

Let's examine several examples of the MySQL RIGHT function and see how to utilize it in MySQL.

Mysql> SELECT RIGHT('Tech on the net', 1);

Result: 't'

Mysql> SELECT RIGHT('techonthenet.com', 4);

Result: '.com'

Mysql> SELECT RIGHT('techonthenet.com', 12);

Result: 'onthenet.com'

Mid

You may extract a substring from a string using the MySQL MID function.

The MID function in MySQL has the following syntax:

MID( string, start_position, length )

String - The source string from which the extract was made.

Start_position - The starting point for extraction. 1 is always the initial position in the string.

Length - The maximum amount of characters that can be extracted.

Example

Let's have a look at some MySQL MID function examples and see how to use it in MySQL.

Mysql> SELECT MID('technical', 5, 2);

Result: 'ni'

Mysql> SELECT MID('technical', 1, 4);

Result: 'tech'

Mysql> SELECT MID('google.com', -3, 3);

Result: 'com'

Concat

You can use the MySQL CONCAT function to join two or more expressions together.

The CONCAT function in MySQL has the following syntax:

CONCAT( expression1, expression2, ... Expression_n )

Expression1, expression2, ... Expression_n - The expressions that should be concatenated.

●      If expression is a numeric value, the CONCAT function will convert it to a binary string.

●      The CONCAT function will produce a nonbinary string if all expressions are nonbinary strings.

●      The CONCAT function will return a binary string if any of the expressions are binary strings.

●      The CONCAT function will return a NULL value if any of the expressions are NULL.

Example

Let's look at some instances of the MySQL CONCAT function and see how to use it in MySQL.

Mysql> SELECT CONCAT('The answer is ', 24);

Result: 'The answer is 24'

Mysql> SELECT CONCAT('The answer is ', 10+10);

Result: 'The answer is 20'

Now

The NOW function in MySQL displays the current date and time.

The NOW function in MySQL has the following syntax:

NOW( )

Parameter or arguments - The NOW function does not have any parameters or arguments.

●      If called in a string environment, the NOW function will return the current date in the 'YYYY-MM-DD HH:MM:SS' format.

●      If used in a numeric environment, the NOW function will return the current date in the YYYYMMDDHHMMSS format.

Example

Let's look at several examples of the MySQL NOW function and see how to use it in MySQL.

Mysql> SELECT NOW();

Result: '2014-01-28 13:48:41'

Mysql> SELECT NOW() + 0;

Result: 20140118134841.000000

Mysql> SELECT NOW() + 1;

Result: 20140118134842.000000

Q17) Explain time, curdate, day,month,year,dayname, monthname? 12 Marks

A17) time

TIME retrieves the time value from a time/datetime expression in MySQL.

The TIME function in MySQL has the following syntax:

TIME( expression )

Expression - The time or datetime value that should be used to extract the time.

Example

Let's have a look at some MySQL TIME function examples and see how to use it in MySQL.

Mysql> SELECT TIME('2014-02-14 06:18:01.000001');

Result: '06:18:01.000001'

Mysql> SELECT TIME('2014-02-17 18:20:19');

Result: '18:20:19'

Mysql> SELECT TIME('10:35:05');

Result: '10:35:05'

Mysql> SELECT TIME('The time is 10:35:05');

Result: '00:00:00'

Mysql> SELECT TIME(NULL);

Result: NULL

Date

DATE extracts the date value from a date or datetime expression in MySQL.

The DATE function in MySQL has the following syntax:

DATE( expression )

The date or datetime value that should be used to extract the date.

Example

Let's have a look at some MySQL DATE function examples and see how to use it in MySQL.

Mysql> SELECT DATE('2014-02-14');

Result: '2014-02-14'

Mysql> SELECT DATE('2014-02-14 18:20:19');

Result: '2014-02-14'

Mysql> SELECT DATE('2014-02-15 06:18:01.000001');

Result: '2014-02-15'

Mysql> SELECT DATE('The date is 2014-02-14');

Result: NULL

Mysql> SELECT DATE(NULL);

Result: NULL

Curdate

The current date is returned by the MySQL CURDATE function.

The CURDATE function in MySQL has the following syntax:

CURDATE( )

Parameter or argument - The CURDATE function does not have any parameters or arguments.

●      If used in a string environment, the CURDATE function will return the current date in the 'YYYY-MM-DD' format.

●      If used in a numeric environment, the CURDATE function will return the current date in the YYYYMMDD format.

Example

Let's look at some examples of the MySQL CURDATE function and see how to use it in MySQL.

Mysql> SELECT CURDATE();

Result: '2014-01-28'

Mysql> SELECT CURDATE() + 0;

Result: 20140128

Mysql> SELECT CURDATE() + 1;

Result: 20140129

Day

The DAY function in MySQL returns the day component of a date.

The DAY function in MySQL has the following syntax:

DAY( date_value )

Date_value - The date or datetime value from which the day will be extracted.

Example

Let's look at several instances of the MySQL DAY function and see how to use it in MySQL.

Mysql> SELECT DAY('2014-01-28');

Result: 28

Mysql> SELECT DAY('2014-01-28 15:21:05');

Result: 28

Mysql> SELECT DAY('2013-10-15');

Result: 15

Month

The MONTH function in MySQL returns the month component of a date.

The MONTH function in MySQL has the following syntax:

MONTH( date_value )

Date_value - The month is extracted from a date or datetime value.

Example

Let's look at several instances of the MySQL MONTH function and see how to use it in MySQL.

Mysql> SELECT MONTH('2014-01-28');

Result: 1

Mysql> SELECT MONTH('2014-01-28 15:21:05');

Result: 1

Mysql> SELECT MONTH('2013-10-15');

Result: 10

Year

The YEAR function in MySQL returns the year component of a date data.

The YEAR function in MySQL has the following syntax:

YEAR( date_value )

Date_value - The year is extracted from a date or datetime value.

Example

Let's have a look at some instances of the YEAR function in MySQL and see how to use it.

Mysql> SELECT YEAR('2014-01-28');

Result: 2014

Mysql> SELECT YEAR('2014-01-28 15:21:05');

Result: 2014

Mysql> SELECT YEAR('2013-10-15');

Result: 2013

Dayname

The DAYNAME function in MySQL returns a date's weekday name.

The DAYNAME function in MySQL has the following syntax:

DAYNAME( date_value )

Date_value - The weekday name is extracted from the date or datetime value.

Example

Let's look at several examples of the MySQL DAYNAME function and see how to use it in MySQL.

Mysql> SELECT DAYNAME('2014-01-27');

Result: 'Monday'

Mysql> SELECT DAYNAME('2014-01-28');

Result: 'Tuesday'

Mysql> SELECT DAYNAME('2014-01-29 08:35:17');

Result: 'Wednesday'

Monthname

The MySQL MONTHNAME function delivers the month's entire name for a given date.

The MONTHNAME function in MySQL has the following syntax:

MONTHNAME( date_value )

Date_value - The whole month name can be extracted from a date or datetime data.

Example

Let's have a look at some examples of the MONTHNAME function in MySQL and see how to use it.

Mysql> SELECT MONTHNAME('2014-01-27');

Result: 'January'

Mysql> SELECT MONTHNAME('2014-05-08');

Result: 'May'

Q18) Explain abs,pow, mod, round? 12 Marks

A18) abs

Number1=ABS(number2)

ABS returns a number's absolute value, which is the magnitude of the value without any minus sign.

SET var1=ABS(2.143);      →  2.143

SET var2=ABS(-10);        →  10

SET var3=ABS(10);         →  10

SET var4=ABS(-2.3);       →  2.3

Pow

The POW function in MySQL returns m to the nth power.

The POW function in MySQL has the following syntax:

POW( m, n )

m - The numerical value of something. It is the calculation's starting point.

n - The numerical value of something. It's the exponent that's employed in the equation.

Example

Let's have a look at some instances of the POW function in MySQL and see how to use it.

Mysql> SELECT POW(5, 2);

Result: 25

Mysql> SELECT POW(5, -2);

Result: 0.04

Mysql> SELECT POW(5.5, 3);

Result: 166.375

Mysql> SELECT POW(0, 4);

Result: 0

Mysql> SELECT POW(4, 0);

Result: 1

Mod

The residual of n divided by m is returned by the MySQL MOD function.

The MOD function in MySQL has the following syntax:

MOD( n, m )

OR

n MOD m

OR

n % m

n - The value by which m will be divided.

m - The number of times the value will be divided by n.

Example

Let's have a look at some MySQL MOD function examples and see how to apply it in MySQL.

Mysql> SELECT MOD(12, 5);

Result: 2

Mysql> SELECT MOD(12, 0.18);

Result: 0.12

Mysql> SELECT MOD(100, 3.5938);

Result: 2.9674

Mysql> SELECT 12 MOD 5;

Result: 2

Mysql> SELECT 12 MOD 0.18;

Result: 0.12

Mysql> SELECT 100 MOD 3.5938;

Result: 2.9674

Round

The ROUND function in MySQL returns a value that has been rounded to a specified number of decimal places.

The ROUND function in MySQL has the following syntax:

ROUND( number, [ decimal_places ] )

Number - The number that should be rounded.

Decimal_places - The number of decimal places that should be rounded to. This value must be an integer that is either positive or negative. The ROUND function will round the number to 0 decimal places if this parameter is omitted.

Example

Let's have a look at some instances of the ROUND function in MySQL and see how to use it.

Mysql> SELECT ROUND(125.315);

Result: 125

Mysql> SELECT ROUND(125.315, 0);

Result: 125

Mysql> SELECT ROUND(125.315, 1);

Result: 125.3

Mysql> SELECT ROUND(125.315, 2);

Result: 125.32

Mysql> SELECT ROUND(125.315, -1);

Result: 130

Mysql> SELECT ROUND(125.315, -2);

Result: 100

Mysql> SELECT ROUND(-125.315);

Result: -125

Q19)What is the primary key? 5 Marks

A19) Each record in a table is uniquely identified by the PRIMARY KEY constraint.

Primary keys cannot have NULL values and must have UNIQUE values.

A table can only have ONE primary key, and this primary key can be made up of one or more columns (fields).

Primary key on create table

When the "Persons" table is created, the following SQL creates a PRIMARY KEY on the "ID" column:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (ID)

);

Use the following SQL syntax to name a PRIMARY KEY constraint and to define a PRIMARY KEY constraint on multiple columns:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);

Drop a Primary key constraints

Use the following SQL to remove a PRIMARY KEY constraint:

ALTER TABLE Persons

DROP PRIMARY KEY;

Example

ALTER TABLE Persons

DROP CONSTRAINT PK_Person;

Q20) Define Foreign key? 8 Marks

A20) The FOREIGN KEY constraint is used to prevent activities from destroying table linkages.

A FOREIGN KEY is a field (or set of fields) in one table that refers to the PRIMARY KEY in a different table.

The child table is the one with the foreign key, while the referred or parent table is the one with the primary key.

Take a look at the two tables below:

Persons Table

Orders Table

​​

It's worth noting that the "Orders" table's "PersonID" column refers to the "Persons" table's "PersonID" column.

The "PersonID" column in the "Persons" database serves as the table's PRIMARY KEY.

The "Orders" table's "PersonID" column is a FOREIGN KEY in the "Orders" table.

Because it must be one of the entries in the parent database, the FOREIGN KEY constraint prevents erroneous data from being placed into the foreign key column.

Foreign key on create table

When the "Orders" table is created, the following SQL generates a FOREIGN KEY on the "PersonID" column:

CREATE TABLE Orders (

OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

);

Drop a foreign key

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders

DROP FOREIGN KEY FK_PersonOrder;