/* 031- Arithmetic Functions */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","19","M",1,1),
(2,"Sarita Kumari","85","22","F",2,2),
(3,"Salman Khan","29","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,3),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","120","20","M",1,1);
-- Arithmetic Functions --
SELECT 5 + 6;
SELECT 5 + 6 AS Total;
SELECT 5 - 6 AS Total;
SELECT 5 * 6 AS Total;
SELECT 15 / 6 AS Total;
SELECT 15 DIV 6 AS Total;
SELECT 15 % 6 AS Total;
SELECT 15 MOD 6 AS Total;
SELECT id,name,percentage FROM students;
SELECT id,name,(percentage + 5) FROM students;
SELECT id,name,(percentage + 5) AS "NEW Percentage" FROM students;
SELECT id,name,(percentage * 5) AS "NEW Percentage" FROM students;
SELECT PI();
SELECT ROUND(4.51);
SELECT ROUND(4.49);
SELECT ROUND(-4.49);
SELECT ROUND(-4.55);
SELECT ROUND(1234.987);
SELECT ROUND(1234.987,2);
SELECT CEIL(1.23);
SELECT CEIL(1.56);
SELECT CEIL(4.23);
SELECT FLOOR(4.56);
SELECT FLOOR(4.40);
SELECT POW(2,2);
SELECT POW(2,3);
SELECT POW(4,3);
SELECT SQRT(16);
SELECT SQRT(4);
SELECT SQRT(5);
SELECT ROUND(SQRT(5));
SELECT RAND();
SELECT RAND() * 100;
SELECT ROUND(RAND() * 100);
SELECT FLOOR(7 + (RAND() * 6));
SELECT FLOOR(1 + (RAND() * 5));
SELECT id,name,percentage,RAND() FROM students;
SELECT id,name,percentage FROM students ORDER BY RAND();
SELECT ABS(-56);
SELECT ABS(-56.25);
SELECT ABS(56.25);
SELECT SIGN(25);
SELECT SIGN(0);
SELECT SIGN(-25);
SELECT SIGN(-3.25);