Scribd
Upload
2 views1 page

SQL Database Setup for University Professors

The document outlines SQL commands for creating and managing a university database named 'university_db02'. It includes creating a 'professors' table with constraints, inserting records, and querying data for various analyses. Additionally, it demonstrates the creation of an index and a view for better data management and retrieval.

Uploaded by

mayurisomnath1755
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
2 views1 page

SQL Database Setup for University Professors

The document outlines SQL commands for creating and managing a university database named 'university_db02'. It includes creating a 'professors' table with constraints, inserting records, and querying data for various analyses. Additionally, it demonstrates the creation of an index and a view for better data management and retrieval.

Uploaded by

mayurisomnath1755
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

create database university_db02;

use university_db02
create table professors(prof_id int primary key,prof_name varchar(20) not
null,department varchar(20) not null,salary decimal(10,2));
desc professors;
create index idx_prof_name on professors(prof_name);
alter table professors add constraint uc_name_dept unique(prof_name,department);
alter table professors add constraint chk_salary_positive check(salary>0);
create view prof_details as select prof_name,department from professors;
insert into professors (prof_id,prof_name,department,salary)values
(101,'abc','aids',5000), (102,'qwe','comp',-2);
insert into professors (prof_id,prof_name,department,salary)values
(104,'akc','aids',500), (902,'owe','comp',2), (173,'vlh','aids',700);
select * from professors;
select department from professors where prof_name='abc';
select prof_name from professors where prof_name like 'a%';
select prof_name from professors where department='aids';
select distinct department from professors;
select count(*) as num_profs from professors where salary>700;
select department, sum(salary) as agg_salary from professors group by department;

You might also like