1.Create a student table with the student id, name, and marks as attributes where the student id is the primary key.
CREATE TABLE student (studentid int(3) primary key, name varchar(20) not null, marks decimal(5,2));
2.Insert the details of a new student in the above table.
INSERT INTO student(1,"Arpit",78.50);
INSERT INTO student(1,"Bharti",87.50);
INSERT INTO student(1,"Chintu",62.50);
INSERT INTO student(1,"Charmi",89.50);
INSERT INTO student(1,"Divya",80.50);
3.Write a query to display the data in student table.
SELECT * FROM student;
4.Delete the details of a student in the above table.
DELETE FROM student WHERE studentid=5;
5.Use the select command to get the details of the students with marks more than 80.
SELECT * FROM student WHERE marks>80;
6.Find the min, max, sum, and average of the marks in a student marks table.
SELECT max(marks), min(marks), sum(marks) , avg(marks) FROM student;
7.Find the total number of customers from each country in the table (customer ID, customer Name, country) using group by.
SELECT country, count(customer_id) FROM customer GROUP BY country;
8.Write a SQL query to order the (student ID, marks) table in descending order of the marks.
SELECT * FROM student ORDER BY marks DESC;
9.Write a SQL query to display the marks without decimal places, display the reminder after diving marks by 3 and display the square of marks.
SELECT round(marks,0),mod(marks,3),pow(marks,2) FROM student;
10.Write a SQL query to display names into capital letters, small letters, display frist 3 letters of name, display last 3 letters of name, display the position the letter A in name,
SELECT ucase(name), lcase(name), left(name,3),right(name,3), instr(name,'a') FROM student;
11.Write a query to remove extra spaces from left, right and both sidesfrom the text - " Informatics Practices Class XII ".
SELECT ltrim(" Informatics Practices Class XII ") AS "Left Spaces", rtrim(" Informatics Practices Class XII ") AS "Right Trim", trim(" Informatics Practices Class XII ") AS "both";
12.Write a query to display today's date in "Date/Month/Year" format.
SELECT concat(date(now()), concat("/",concat(month(now()), concat("/",year(now()))))) AS "todays Date";
13.Write a query display dayname, monthname, day, dayname, day of month, day of year for today's date.
SELECT dayname(now()), monthname(now()), day(now()), dayname(now()), dayofmonth(now()), dayofyear(now());
