Download Practical test for database management system and more Exams Database Management Systems (DBMS) in PDF only on Docsity!
use uni; -- highest avg salary by dept select dept_name ,avg (salary) from instructor group by dept_name order by avg(salary) desc limit 1 offset 0; create schema test; use test; CREATE TABLE COMPANY( EMPLOYEE_ID INT PRIMARY KEY, EMPLOYEE_NAME VARCHAR(10), DEPARTMENT_NAME VARCHAR(10), SALARY INT); INSERT INTO COMPANY VALUES(1,'RAM','HR',10000); INSERT INTO COMPANY VALUES(2,'AMRIT','MRKT',20000); INSERT INTO COMPANY VALUES(3,'RAVI','HR',30000); INSERT INTO COMPANY VALUES(4,'NITIN','MRKT',40000); INSERT INTO COMPANY VALUES(5,'VARUN','IT',50000); show tables; -- set comparison -- max >= all > some or min <= all emp <some SELECT DEPARTMENT_NAME, count() as Num_of_emp FROM COMPANY GROUP BY DEPARTMENT_NAME having count() < some (select count(*) from company group by department_name); use test; alter table company rename emp; select * from emp; alter table emp rename column employee_id to emp_id; alter table emp rename column employee_name to emp_name; alter table emp rename column DEPARTMENT_NAME to dept; desc emp; create table project (emp_id int, pid varchar(20), pname varchar(30),
location varchar(40), primary key (pid), foreign key (emp_id) references emp (emp_id) ); create schema friendship; use friendship; create table user( id INT auto_increment not null, full_name VARCHAR(255) not null, username VARCHAR(255) not null, bio VARCHAR(255) not null, date_of_birth DATE, primary key(id) ); create table friendship( id INT auto_increment not null, fk_friend_one_id INT not null, fk_friend_two_id INT not null, primary key(id), foreign key (fk_friend_one_id) references user (id) on delete cascade, foreign key (fk_friend_two_id) references user (id) on delete cascade); create table post( id INT auto_increment not null, fk_poster_id INT not null, text_body TEXT not null, primary key (id), foreign key (fk_poster_id) references user(id) on delete cascade ); create table likes( id INT auto_increment, fk_user_id INT not null, fk_post_id INT not null, primary key (id), foreign key (fk_user_id) references user(id) on delete cascade, foreign key (fk_post_id) references post(id) on delete cascade ); insert into user values ('1','Linus Torvalds aaaa', 'linus aaaa', 'Works on Linux and Git!adada','1970-10- 19'), ('2','Dextar','dextar','I love technology!','1995-10-1'), ('3','Ron Macdonald','ron_mac','Ron. CEO. Father','1969-01-01'), ('4','Chad Thundar','chad_thunder','This is Chad!','1980-10-10'); insert into friendship values
SET SQL_SAFE_UPDATES=0;
update actor set first_name = 'HARPO' and last_name ='WILLIAMS' where first_name ='GROUCHO ' and last_name ='WILLIAMS'; SET SQL_SAFE_UPDATES=1; select first_name,last_name from actor where last_name = 'WILLIAMS'; select first_name, last_name, email from customer where address_id in( select address_id from address where city_id in (select city_id from city where country_id in (select country_id from country where country ='Canada') ) ); with info (id) as( select fk_friend_two_id from friendship where fk_friend_one_id = 1 ) select * from user natural join info; use friendship;
