Database Management System Assignment Questions Answers
कोई प्रश्न रहा जाता है ही तो नीचे कॉमेंट में बताए
Write a query to create a table and view the table स्ट्रक्चर
To create a table in MySQL, you need to use the CREATE TABLE statement. Here's a basic example of how to create a simple table called "users", which contains three columns ("id", "name", and "email"):
```
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
```
This statement creates a table 'users' with three columns; an auto-incrementing integer column called 'id', a string column called 'name', and another string column called 'email'. The 'id' column is set to be the primary key, which means each row will have a unique value in that column.
To view the table structure, you can use the DESCRIBE statement or SHOW CREATE TABLE. Here's an example:
```
DESCRIBE users;
```
This will output a table showing the name of each column, the data type, and any additional information about each column. Alternatively, you could use the SHOW CREATE TABLE statement like this:
```
SHOW CREATE TABLE users;
```
This will output the full table creation statement, including all column definitions, indexes, and any other table-level options you may have specified.
Write a query to insert data into the table
Sure, here's an example of how you can insert data into the "users" table we created earlier:```
INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');
```
This statement inserts a new row into the "users" table with the name "John Doe" and email "johndoe@example.com". Since we didn't specify a value for the "id" column, MySQL will automatically generate a unique value for this column.
If you want to insert multiple rows at once, you can use a single INSERT statement with multiple sets of values, like this:
```
INSERT INTO users (name, email) VALUES
('John Doe', 'johndoe@example.com'),
('Jane Smith', 'janesmith@example.com'),
('Bob Johnson', 'bobjohnson@example.com');
```
This statement inserts three rows into the "users" table with the names "John Doe", "Jane Smith", and "Bob Johnson", along with their respective email addresses. Each pair of values is enclosed in parentheses and separated by a comma.
Write a queries to view the table data.
Sure, here are some examples of queries you can use to view the data in the "users" table we created earlier:
To select all columns and rows from the table:
```
SELECT * FROM users;
```
This query will return all rows and columns from the "users" table.
To select only specific columns:
```
SELECT name, email FROM users;
```
This query will only return rows with the "name" and "email" columns.
To filter the results by a specific value:
```
SELECT * FROM users WHERE name = 'John Doe';
```
This query will only return rows where the "name" column is 'John Doe'.
To sort the results by a specific column:
```
SELECT * FROM users ORDER BY name ASC;
```
This query will return all rows, sorted by the "name" column in ascending order. To sort in descending order, use "DESC" instead of "ASC".
And lastly, to count the number of rows in the table:
```
SELECT COUNT(*) FROM users;
```
This query will only return a single column with the total number of rows in the "users" table.
Write a query to sort data into the table and eliminate duplicate records
To sort data and eliminate duplicate records in a table using DBMS software, you can use the following SQL query:
```
SELECT DISTINCT *
FROM table_name
ORDER BY column_name ASC/DESC;
```
In this query, replace `table_name` with the name of your table and `column_name` with the name of the column you want to sort by. The `DISTINCT` keyword ensures that only unique records are returned.
You can also specify multiple columns for sorting by separating them with commas:
```
SELECT DISTINCT *
FROM table_name
ORDER BY column1_name ASC/DESC, column2_name ASC/DESC;
```
This query will first sort the records by `column1_name` and then by `column2_name`.
Write queries to perform delete operation into the table.
To perform a delete operation in a table using DBMS software, you can use the following SQL query:
```
DELETE FROM table_name
WHERE condition;
```
In this query, replace `table_name` with the name of your table and `condition` with the condition that specifies which records to delete. For example, if you want to delete all records where the `id` column is equal to 1, you would use the following query:
```
DELETE FROM table_name
WHERE id = 1;
```
You can also use multiple conditions with logical operators such as `AND` and `OR`. For example, if you want to delete all records where the `id` column is equal to 1 and the `name` column is equal to "John", you would use the following query:
```
DELETE FROM table_name
WHERE id = 1 AND name = 'John';
```
Write queries to updating the contain of a table.
To update the contents of a table using DBMS software, you can use the following SQL query:
```
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```
In this query, replace `table_name` with the name of your table, `column1` and `column2` with the names of the columns you want to update, and `value1` and `value2` with the new values you want to set. You can update multiple columns at once by separating them with commas.
You also need to specify a `condition` that identifies which records to update. For example, if you want to update the `name` column to "Jane" for all records where the `id` column is equal to 1, you would use the following query:
```
UPDATE table_name
SET name = 'Jane'
WHERE id = 1;
```
You can also use logical operators such as `AND` and `OR` to specify multiple conditions. For example, if you want to update the `name` column to "Jane" and the `age` column to 30 for all records where the `id` column is equal to 1 and the `gender` column is equal to "Female", you would use the following query:
```
UPDATE table_name
SET name = 'Jane', age = 30
WHERE id = 1 AND gender = 'Female';
```
Write queries to modifying the structure of the table.
To modify the structure of a table using DBMS software, you can use various SQL queries depending on the changes you want to make. Here are some examples:
1. Adding a new column to a table:
```
ALTER TABLE table_name
ADD new_column_name data_type;
```
In this query, replace `table_name` with the name of your table and `new_column_name` with the name of the new column you want to add. You also need to specify the data type for the new column.
2. Modifying an existing column in a table:
```
ALTER TABLE table_name
MODIFY column_name new_data_type;
```
In this query, replace `table_name` with the name of your table and `column_name` with the name of the column you want to modify. You also need to specify the new data type for the column.
3. Renaming a column in a table:
```
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
```
In this query, replace `table_name` with the name of your table, `old_column_name` with the current name of the column, and `new_column_name` with the new name you want to give to the column.
4. Dropping a column from a table:
```
ALTER TABLE table_name
DROP COLUMN column_name;
```
In this query, replace `table_name` with the name of your table and `column_name` with the name of the column you want to drop.
Note that some DBMS software may have slightly different syntax for these queries, so consult your software's documentation for specific instructions. Also, be careful when modifying the structure of a table as it can affect any queries or programs that rely on that table.
Write a query to Renaming, Truncating and Destroying table.
Here is an example query that renames, truncates, and destroys a table:
```
-- Rename a table
ALTER TABLE old_table_name
RENAME TO new_table_name;
-- Truncate a table (delete all rows)
TRUNCATE TABLE table_name;
-- Destroy a table (drop the entire table)
DROP TABLE table_name;
```
In the first query, replace `old_table_name` with the current name of the table and `new_table_name` with the new name you want to give to the table.
In the second query, replace `table_name` with the name of the table you want to truncate. Note that this will delete all rows in the table, so be sure to back up any important data before running this query.
In the third query, replace `table_name` with the name of the table you want to destroy. This will drop the entire table, including all rows and columns, so again, make sure you have a backup of any important data before running this query.
Write a query for pattern matching.
To perform pattern matching, we can use the LIKE operator along with wildcard characters (% and _). Here's an example query:
SELECT * FROM table_name
WHERE column1 LIKE '%pattern%';
This query will return all rows from the table where the value in column1 contains the word "pattern" anywhere within it. The % wildcard character represents zero or more characters, so this query will also match values like "mypattern" and "patterned". If we wanted to match a specific number of characters, we could use the _ wildcard character instead. For example:
SELECT * FROM table_name
WHERE column1 LIKE 'abc_';
This query will return all rows where the value in column1 starts with "abc" followed by any single character.
Write queries to perform aggregate function
To perform aggregate functions, we use the SELECT statement along with one or more of the following functions:
- COUNT: returns the number of rows that match the query
- SUM: returns the sum of a numeric column
- AVG: returns the average of a numeric column
- MAX: returns the highest value in a column
- MIN: returns the lowest value in a column
Here are some examples of queries using aggregate functions:
1. To count the number of rows in a table:
SELECT COUNT(*) FROM table_name;
2. To calculate the sum of a column:
SELECT SUM(column_name) FROM table_name;
3. To calculate the average of a column:
SELECT AVG(column_name) FROM table_name;
4. To find the highest value in a column:
SELECT MAX(column_name) FROM table_name;
5. To find the lowest value in a column:
SELECT MIN(column_name) FROM table_name;
Note that aggregate functions can also be used with the WHERE clause to filter the results before performing the calculation. For example:
SELECT COUNT(*) FROM table_name WHERE column_name = 'value';