Dbms assignment 3(a) (1)

Jul 30, 20200 likes99 views

The document describes SQL queries performed on database tables for a university department and faculty database. The tables created are Departments and Faculty. Data is inserted and constraints are added. Joins are used to find department names and heads. Attributes like salary are added. Queries find faculty by department, those earning a certain salary range, departments by faculty count, and more.

Career

#DBMS assignment 3(a)
#Author : Soumak Paul.
#Create table: DEPARTMENT
CREATE TABLE DEPARTMENT(
DeptCode varchar(4) NOT NULL,
DeptName varchar(40) NOT NULL,
HOD varchar(4),
PRIMARY KEY(DeptCode));
#Insert values into DEPARTMENT table.
INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD)
VALUES('IT','Information Technology','F201');
INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD)
VALUES('BIO','Bio Technology','F901');
INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD)
VALUES('CSE','Computer Science and Engineering,'F101');
INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD)
VALUES('NS','Natural Science','F506');
#Add a foreign key constraint in STUDENT against DeptCode column which
references department.
ALTER TABLE STUDENT
ADD FOREIGN KEY (DeptCode) REFERENCES DEPARTMENT(DeptCode);
#Create table: FACULTY
CREATE TABLE FACULTY(
FacultyCode varchar(4) NOT NULL,
FacultyName varchar(15) NOT NULL,
DateOfJoin date NOT NULL,
DeptCode varchar(4) NOT NULL,
PRIMARY KEY (FacultyCode));
#Insert values into FACULTY table.
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F101','M. Sinha','2005-01-01','CSE');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F105','P. Sarkar','2019-02-01','CSE');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F201','S. Mazumder','2005-09-15','IT');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F301','S. Mondal','2018-08-01','CSE');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F401','D. Majumdar','2003-12-01','IT');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F506','N. Biswas','2013-12-31','NS');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)

VALUES('F607','R. Paul','2007-04-10','BIO');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F704','S. Sarkar','2012-01-01','IT');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F808','K. Das','2010-06-15','IT');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F901','R. Roy','2017-06-15','BIO');
INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)
VALUES('F902','R. Biswas','2018-06-15','BIO');
#Alter the table Faculty and add check constraint such that FacultyCode starts
with ‘F’
ALTER TABLE FACULTY
ADD CONSTRAINT FacultyCodeCheck CHECK (FacultyCode like 'F%');
#Alter the table Faculty and add check constraint such DeptCode is either
CSE,IT, BIO,NS
ALTER TABLE FACULTY
ADD CONSTRAINT DeptCodeCheck CHECK (DeptCode IN ('CSE','IT','NS','BIO'));
#Add constraint : DeptCode of Faculty is foreign key and references DeptCode
in Department.
ALTER TABLE FACULTY
ADD FOREIGN KEY (DeptCode) REFERENCES DEPARTMENT(DeptCode);
#Add Constraint: HOD of Department table is foreign key and references
FacultyCode of Faculty.
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (HOD) REFERENCES FACULTY(FacultyCode);
#Find the names of faculties of CSE Department.
SELECT FacultyName FROM FACULTY WHERE DeptCode = 'CSE';
#Find the number of faculties in the IT department
SELECT COUNT(*) FROM FACULTY WHERE DeptCode = 'IT';
#Find the number of faculties who joined in August?.
SELECT COUNT(*) AS 'Hired in August' FROM FACULTY
WHERE (MONTH(DateOfJoin) = 8);
#Show the names of the heads of departments with department name.
SELECT FACULTY.DeptCode AS 'DEPARTMENT NAME', FACULTY.FacultyName AS 'HOD NAME'
FROM FACULTY
INNER JOIN DEPARTMENT ON FACULTY.DeptCode = DEPARTMENT.DeptCode
GROUP BY DEPARTMENT.DeptCode
ORDER BY DEPARTMENT.DeptCode;

#Add an extra attribute to the faculty table - Salary Number(8,2)
ALTER TABLE FACULTY
ADD COLUMN SalaryNumber DECIMAL(8,2) AFTER DeptCode;
#Insert values into the corresponding field Salary Number(8,2) (Enter distinct
values).
UPDATE FACULTY SET SalaryNumber = '25000.00' WHERE FacultyCode = 'F101';
UPDATE FACULTY SET SalaryNumber = '15000.00' WHERE FacultyCode = 'F105';
UPDATE FACULTY SET SalaryNumber = '18000.00' WHERE FacultyCode = 'F201';
UPDATE FACULTY SET SalaryNumber = '16500.00' WHERE FacultyCode = 'F301';
UPDATE FACULTY SET SalaryNumber = '16000.00' WHERE FacultyCode = 'F401';
UPDATE FACULTY SET SalaryNumber = '18000.00' WHERE FacultyCode = 'F506';
UPDATE FACULTY SET SalaryNumber = '15000.00' WHERE FacultyCode = 'F607';
UPDATE FACULTY SET SalaryNumber = '11000.00' WHERE FacultyCode = 'F704';
UPDATE FACULTY SET SalaryNumber = '13000.00' WHERE FacultyCode = 'F808';
UPDATE FACULTY SET SalaryNumber = '20000.00' WHERE FacultyCode = 'F901';
UPDATE FACULTY SET SalaryNumber = '14000.00' WHERE FacultyCode = 'F902';
#Find the Department having more than one faculty.
SELECT DEPARTMENT.DeptCode
FROM FACULTY
INNER JOIN DEPARTMENT
ON FACULTY.DeptCode = DEPARTMENT.DeptCode
GROUP BY DEPARTMENT.DeptCode
HAVING COUNT(*) > 1;
#Find the name, department of the faculties who earn between 8000 and 12000.
SELECT FacultyName, DeptCode FROM FACULTY
WHERE (SalaryNumber >= 8000 AND SalaryNumber <= 12000);
#Find the name of the department with maximum faculties.
SELECT DeptName, COUNT(*) AS DEPTCOUNT FROM FACULTY
JOIN DEPARTMENT ON FACULTY.DeptCode = DEPARTMENT.DeptCode
GROUP BY DeptName
ORDER BY DEPTCOUNT DESC
LIMIT 1;
#Find the senior-most faculty.
SELECT * FROM FACULTY
ORDER BY DateOfJoin
LIMIT 1;
#Find the name of the faculty who has completed 5 years?.
SELECT FacultyName FROM FACULTY
WHERE (DATEDIFF(CURDATE(),DateOfJoin) > 1826);

Using DDL Statements to Create and Manage Tables The document discusses using DDL statements to create and manage database tables. It covers categorizing database objects like tables, views, indexes, and sequences. It also covers the syntax for creating tables, specifying column names and data types, creating constraints, and dropping tables. Examples are provided for creating a simple table, adding constraints, creating a table using a subquery, and altering or dropping existing tables.

Les18Vijay Kumar

This document discusses advanced subquery techniques in SQL, including: 1) Writing multiple-column subqueries that can perform pairwise or nonpairwise comparisons. 2) Using scalar subqueries that return a single value in more clauses and expressions. 3) Writing correlated subqueries that are executed once per row to filter or update related data. 4) Using the EXISTS operator to check for matching rows in a subquery results set. 5) Leveraging the WITH clause to reuse query blocks and improve performance of complex queries.

Les17Vijay Kumar

rdbms practical recordShivaraju KoilaKonda

This document contains SQL queries to create tables, insert records, update records, and perform aggregation functions on sample employee and student data. The key steps are: 1) Create tables to store student and employee data with various fields 2) Insert sample records into the tables 3) Update records by calculating totals, averages, and increasing/decreasing field values 4) Use aggregation functions like count, sum, avg to analyze the data 5) Display records by filtering on field values

Very simple queries of DBMSmohit tripathi

The document discusses various SQL queries and commands for creating, manipulating, and retrieving data from multiple tables. It includes commands for creating tables, inserting data, updating records, deleting records, and joining tables. It also covers creating views and PL/SQL blocks. Sample queries demonstrate aggregation functions, subqueries, and working with foreign keys across different tables.

DBMS Notes selection projection aggregateSreedhar Chowdam

4. DML.pdfSunita Milind Dol

The document discusses the SQL INSERT command which is used to insert data into tables in a database. It describes the basic syntax for inserting a single row with and without specifying column names. It also provides the syntax for inserting multiple rows or selecting data from one table to insert into another table. The UPDATE command is also summarized, providing the syntax for updating column values in a table.

lab.123456789123456789123456789123456789Ghh

The document consists of various SQL programs that create and manipulate database tables related to students, courses, grades, employees, books, train details, financial accounts, and equipment details. Each program includes commands for creating tables, inserting values, selecting data, updating records, and deleting entries. These operations demonstrate fundamental database management skills in SQL, such as creating relationships between tables and performing data queries.

Relational Database Managment System Lab - Group AMurugan146644

The document contains a detailed guide on creating student and sales order tables using SQL, including instructions for inserting, updating, and displaying records. Various exercises illustrate integrity rules, handling data, and performing queries such as calculating totals, modifying records, deleting entries, and ensuring data validity. Example queries demonstrate the creation and manipulation of tables for student information and sales order management.

YASH DBMS PRACTICAL FILE DIGVIJAY COLLAGE .pdfyash production

The document provides SQL statements for creating and managing a database system related to colleges and staff, including tables for colleges, staff, staff joins, teachings, and subjects. It includes SQL commands to create these tables, insert sample data, and execute various queries to retrieve information such as teachers teaching specific subjects and staff salary comparisons. Additionally, it details the creation of another database for enrollment and admissions with corresponding table structures and constraints.

MdnanaJenglot

The document describes the creation of several database tables - student, faculty, course, grade, presentation, and grade_scale. It inserts sample data into the student, faculty, course, and grade tables. It also modifies some of the table structures, such as increasing the size of the address field in student and adding primary keys to course_id in grade and fac_id in faculty to prevent redundant data.

create_FinalDB.sql ---------------------------------------.docxfaithxdunce63732

The document contains SQL scripts for creating, populating, and dropping a database named 'finaldb' for a school management system, including tables for students, courses, assignments, scores, class levels, and traditional types. It includes data inserts for class levels, student details, course descriptions, assignments, and results. The script also demonstrates how to drop tables and constraints associated with the database.

MergeResult_2023_11_05_10_40_21.pptxooooPriyanka77088

The document outlines a database system design assignment for managing student information at a university, including details such as names, student numbers, social security numbers, addresses, and academic departments. It describes the structure of tables for students, departments, courses, and sections, including keys and references for data integrity. Several example entries for students, departments, sections, and courses are also provided to illustrate the proposed database schema.

DDlMuhammad Zubair

Sql server query collectionRabin Koirala

This document provides an overview of SQL Server concepts and techniques including: 1. Creating and modifying databases, tables, views, constraints, and data types. 2. Performing queries using SELECT statements, joins, subqueries, CTEs, aggregate functions, and ranking functions. 3. Inserting, updating, and deleting data. 4. Implementing stored procedures, user-defined functions, triggers, and error handling. The document includes examples for each concept to demonstrate how to implement them in SQL Server.

SQL Queries and Solutions (Database)SM. Aurnob

The document outlines a university database schema consisting of multiple tables such as department, instructor, student, course, section, teaches, and takes. It includes SQL statements for creating tables, finding course titles, instructor salaries, and making updates and deletions based on certain criteria. Various queries demonstrate the retrieval and manipulation of data concerning departments, instructors, courses, and student enrollments.

SQL Practice Question set Mohd Tousif

5. Basic Structure of SQL Queries.pdfSunita Milind Dol

This document discusses SQL queries and provides examples of different types of queries: 1) Queries on a single relation to retrieve instructor names, department names, and salaries. 2) Queries with joins across multiple relations to retrieve instructor names, department names, and building names. 3) Queries to find instructor names and courses taught using a Cartesian product between the instructor and teaches relations.

On SQL Managment studioThis lab is all about database normalizatio.pdfinfomalad

This document outlines a lab focused on database normalization using SQL Management Studio, which involves creating and manipulating four types of databases: Unnormalized Form (UNF), First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). It includes specific instructions for creating tables, inserting data, and executing queries to demonstrate how normalization affects data querying. The lab is designed to ensure that the same result set is returned across the different normalized databases, serving as a validation of the queries executed.

Relational DB Course Sunny U Okoro

The document outlines activities in an object relational database course including creating object types and tables to store student data with object attributes, inserting records into the tables, and performing queries on the data including aggregation, ranking, and window functions. Object types are defined for people and students, and tables are created to store contact and student data using the object types.

Sql (Introduction to Structured Query language)Mohd Tousif

The document outlines SQL commands for creating a database, establishing connections, creating tables, and performing operations such as insertions, updates, deletions, and data retrieval using select statements. It includes examples of aggregate functions, union operations, and various SQL join types, demonstrating how to manipulate and query data from database tables. Additionally, it covers using wildcards and the IN operator for more complex queries.

Here is the company database for the problem--commen.pdffazilfootsteps

The document outlines the creation and manipulation of an employee database within Oracle, including table structures for employee, department, project, dependent, and works_on tables. It contains SQL commands to create, drop, and modify these tables, as well as insert employee and project data, while detailing foreign key relationships. Additionally, it explains how ownership of database objects impacts queries and permissions, emphasizing the necessity of using the correct account in SQL statements.

Sql file (1)rajkumari873

The document contains examples of creating tables, inserting data, and performing various SQL queries on the tables. It includes: 1. Creating a Customer table with constraints and inserting 6 records. 2. Creating an Employee table with constraints and inserting 10 records. Queries are provided to select names and departments, filter by department and salary, and sort results. 3. Additional examples include altering tables to add columns, filtering records by conditions, sorting results, and counting records.

DBMS ASSIGNMENT questions list for graduation .pdfStudyWithBarkha

Tallerpracticaguest4b949be

Tallerpracticabrayanpalomino

Tallerpracticaguest4b949be

Taller 4blaze0002

Revolutionizing Environmental Compliance with AI.pdfDorian F Corliss

In the face of rising regulatory pressures and growing environmental concerns, industrial sectors must evolve. Traditional compliance approaches are no longer sufficient for meeting today’s complex sustainability goals. Dorian Corliss, a veteran environmental compliance expert with over 20 years of experience and two emissions technology patents, is at the forefront of this transformation. By integrating artificial intelligence into power, wastewater, and landfill operations, he is setting a new standard for smart, sustainable, and scalable environmental management.

TECHNOLOGY LIVELIHOOD EDUCATIONLESSON EXEMPLARAnnMargrettDuka

More Related Content

Similar to Dbms assignment 3(a) (1) (20)

Relational Database Managment System Lab - Group AMurugan146644

YASH DBMS PRACTICAL FILE DIGVIJAY COLLAGE .pdfyash production

MdnanaJenglot

create_FinalDB.sql ---------------------------------------.docxfaithxdunce63732

MergeResult_2023_11_05_10_40_21.pptxooooPriyanka77088

DDlMuhammad Zubair

Sql server query collectionRabin Koirala

SQL Queries and Solutions (Database)SM. Aurnob

SQL Practice Question set Mohd Tousif

5. Basic Structure of SQL Queries.pdfSunita Milind Dol

On SQL Managment studioThis lab is all about database normalizatio.pdfinfomalad

Relational DB Course Sunny U Okoro

Sql (Introduction to Structured Query language)Mohd Tousif

Here is the company database for the problem--commen.pdffazilfootsteps

Sql file (1)rajkumari873

DBMS ASSIGNMENT questions list for graduation .pdfStudyWithBarkha

Tallerpracticaguest4b949be

Tallerpracticabrayanpalomino

Tallerpracticaguest4b949be

Taller 4blaze0002

Relational Database Managment System Lab - Group AMurugan146644

YASH DBMS PRACTICAL FILE DIGVIJAY COLLAGE .pdfyash production

MdnanaJenglot

create_FinalDB.sql ---------------------------------------.docxfaithxdunce63732

MergeResult_2023_11_05_10_40_21.pptxooooPriyanka77088

DDlMuhammad Zubair

Sql server query collectionRabin Koirala

SQL Queries and Solutions (Database)SM. Aurnob

SQL Practice Question set Mohd Tousif

5. Basic Structure of SQL Queries.pdfSunita Milind Dol

On SQL Managment studioThis lab is all about database normalizatio.pdfinfomalad

Relational DB Course Sunny U Okoro

Sql (Introduction to Structured Query language)Mohd Tousif

Here is the company database for the problem--commen.pdffazilfootsteps

Sql file (1)rajkumari873

DBMS ASSIGNMENT questions list for graduation .pdfStudyWithBarkha

Tallerpracticaguest4b949be

Tallerpracticabrayanpalomino

Tallerpracticaguest4b949be

Taller 4blaze0002

Recently uploaded (20)

Revolutionizing Environmental Compliance with AI.pdfDorian F Corliss

TECHNOLOGY LIVELIHOOD EDUCATIONLESSON EXEMPLARAnnMargrettDuka

Ruchi Bhatia HRGurukul - The Roads less travelledRuchi Bhatia

HR Consultant | Executive Coach | Founder – HRGurukul | IIM, XLRI, IBM Alumna | Karmyogi I started my career in sales with GSK, where I was one of the youngest executives to be promoted to the management cadre. Those five years taught me grit, persuasion, and the power of relationships—skills that continue to shape my leadership style. I then spent 12 transformative years at IBM in various HR roles—Corporate Trainer, Workforce Partner, Career Development Lead, L&D Consultant, and Employer Branding Leader. I was promoted five times in 12 years, earned IBM’s Most Valuable Ambassador Award, and was trusted with high-impact roles that shaped people strategy for a global tech giant. But the most life-changing moment was meeting Marshall Goldsmith in 2009—and being coached by him. It lit a fire in me to coach and empower others. In 2016, I took a bold step. I left the corporate world to create HRGurukul, a platform to serve the HR community through consulting, coaching, and learning. Over the last 7 years, HRGurukul has grown into a trusted name in India’s HR ecosystem. The pandemic years were personally challenging—I battled depression. But I emerged stronger, guided by minimalism, purpose, and inner freedom (Vairagya). I returned with deeper empathy, clarity, and conviction in my mission. Today, I wear multiple hats—with one single purpose: to elevate the HR community. ? Recognition & Impact Top 10 HR Influencer, Economic Times HRWorld (2025) Top 20 HR Influencer, Economic Times HRWorld (2023, 2024) Top 50 HR Leader, ETHRWorld (2021) Recognized by SHRM, PeopleMatters, WeAce, NHRD, SheThePeople, Jombay 40 Under 40 Leader Award, Jombay (2018) IBM Ambassador, Manager's Choice Awards, Rising Star ? Beliefs That Guide Me “Vairagya is not running away from life. It is living life with deep purpose and inner freedom.” Gratitude. Abundance. Win-Win Mindset. Being a coach allows me to empower others to discover their best selves. ?? My Three Anchors Health – My most precious asset Mission – To serve the HR community Relationships – With family, friends, and the community that uplifts me

Biography and career history of Dr. Gabriel CarabelloDr. Gabriel Carabello

Dr. Gabriel Carabello is a board-certified vascular surgeon with over three decades of experience treating patients with complex vascular conditions. A native of Los Angeles, California, he has spent much of his career serving patients in both his hometown and other areas in need, including communities in Arizona and Florida. His work centers on the diagnosis and treatment of conditions such as carotid artery disease, abdominal aortic aneurysms, and peripheral vascular disease.

Power BI Jobs in Jaipur Your Gateway to Data Analytics Careers in Rajasthan.pptxvinay salarite

Unlock your future in data with high-demand Power BI jobs in Jaipur! As businesses across Rajasthan embrace data-driven strategies, skilled Power BI professionals are more valuable than ever. From startups to established enterprises, the demand for data visualization and analytics experts is skyrocketing. Whether you're a fresher or an experienced analyst, Jaipur offers excellent opportunities for career growth in business intelligence and data analytics.

Cost_Effective_Multi_Source_Energy_Harvesting_System1 final 5.pptxAnishNaskar4

Presentation about the current products.pptxikrammustafa51

最新版意大利拉奎拉大学毕业证（础蚕鲍滨尝础毕业证书）原版定制taqyea

2025原版拉奎拉大学毕业证书pdf电子版【q薇1954292140】意大利毕业证办理AQUILA拉奎拉大学毕业证书多少钱？【q薇1954292140】海外各大学Diploma版本，因为疫情学校推迟发放证书、证书原件丢失补办、没有正常毕业未能认证学历面临就业提供解决办法。当遭遇挂科、旷课导致无法修满学分，或者直接被学校退学，最后无法毕业拿不到毕业证。此时的你一定手足无措，因为留学一场，没有获得毕业证以及学历证明肯定是无法给自己和父母一个交代的。【复刻拉奎拉大学成绩单信封,Buy Università degli Studi dell'AQUILA Transcripts】购买日韩成绩单、英国大学成绩单、美国大学成绩单、澳洲大学成绩单、加拿大大学成绩单（q微1954292140）新加坡大学成绩单、新西兰大学成绩单、爱尔兰成绩单、西班牙成绩单、德国成绩单。成绩单的意义主要体现在证明学习能力、评估学术背景、展示综合素质、提高录取率，以及是作为留信认证申请材料的一部分。拉奎拉大学成绩单能够体现您的的学习能力，包括拉奎拉大学课程成绩、专业能力、研究能力。（q微1954292140）具体来说，成绩报告单通常包含学生的学习技能与习惯、各科成绩以及老师评语等部分，因此，成绩单不仅是学生学术能力的证明，也是评估学生是否适合某个教育项目的重要依据！我们承诺采用的是学校原版纸张（原版纸质、底色、纹路）我们工厂拥有全套进口原装设备，特殊工艺都是采用不同机器制作，仿真度基本可以达到100%，所有成品以及工艺效果都可提前给客户展示，不满意可以根据客户要求进行调整，直到满意为止！【主营项目】一、工作未确定，回国需先给父母、亲戚朋友看下文凭的情况，办理毕业证|办理文凭: 买大学毕业证|买大学文凭【q薇1954292140】拉奎拉大学学位证明书如何办理申请？二、回国进私企、外企、自己做生意的情况，这些单位是不查询毕业证真伪的，而且国内没有渠道去查询国外文凭的真假，也不需要提供真实教育部认证。鉴于此，办理意大利成绩单拉奎拉大学毕业证【q薇1954292140】国外大学毕业证, 文凭办理, 国外文凭办理, 留信网认证

Rolph Balgobin - The Different Types of EntrepreneurRolph Balgobin

Gives a structured overview of the skills measured in the DP-700 examthehulk1299

Title 狠狠撸 – Exam name and author/date Exam Objectives – Key knowledge domains (administration, monitoring, security) Instance Administration – Managing SQL instances, firewall rules, performance tuning Database-Level Admin – Tables, indexes, triggers, backups Monitoring & High Availability – Query Store, auto tuning, geo-replication Security & Compliance – RBAC, TDE, auditing Tools & Services – Azure Portal, SSMS, Azure Monitor Exam Preparation Tips – Learning paths, mock tests, community support Resources – Official docs, third-party sites, YouTube Q&A 狠狠撸

PRESENTATION general mathematicsppt.pptxDharylBallarta

Pakistan Economy presentation pakistan.pptxikrammustafa51

Corporate Philanthropy’s Impact on Communities.pdfHassan Dakhlallah

Corporate philanthropy plays a crucial role in fostering positive change within local communities. It refers to the voluntary contributions of businesses to improve social, environmental, and economic conditions through donations, volunteer work, and various charitable initiatives. These efforts not only benefit those in need but also create a ripple effect that enhances a company's reputation and strengthens its connection to the community.

Quarter 3 Program Implementation Review and Performance AssessmentLaMariaAngelicaPunay

Yellow and Purple Doodle Startup Pitch Deck Presentation.pdf.pdfkharadeshreya2210

IISc-CDS-v2xxxxxxxxxxxxxcxxxxxx (1).potxrnkaushal2

最新版美国埃默里大学毕业证（贰尘辞谤测毕业证书）原版定制Taqyea

鉴于此，定制埃默里大学学位证书提升履历【q薇1954292140】原版高仿埃默里大学毕业证(Emory毕业证书)可先看成品样本【q薇1954292140】帮您解决在美国埃默里大学未毕业难题，美国毕业证购买，美国文凭购买，【q微1954292140】美国文凭购买，美国文凭定制，美国文凭补办。专业在线定制美国大学文凭，定做美国本科文凭，【q微1954292140】复制美国Emory University completion letter。在线快速补办美国本科毕业证、硕士文凭证书，购买美国学位证、埃默里大学Offer，美国大学文凭在线购买。如果您处于以下几种情况： ◇在校期间，因各种原因未能顺利毕业……拿不到官方毕业证 ◇面对父母的压力，希望尽快拿到； ◇不清楚认证流程以及材料该如何准备； ◇回国时间很长，忘记办理； ◇回国马上就要找工作，办给用人单位看； ◇企事业单位必须要求办理的 ◇需要报考公务员、购买免税车、落转户口 ◇申请留学生创业基金【复刻一套埃默里大学毕业证成绩单信封等材料最强攻略,Buy Emory University Transcripts】购买日韩成绩单、英国大学成绩单、美国大学成绩单、澳洲大学成绩单、加拿大大学成绩单（q微1954292140）新加坡大学成绩单、新西兰大学成绩单、爱尔兰成绩单、西班牙成绩单、德国成绩单。成绩单的意义主要体现在证明学习能力、评估学术背景、展示综合素质、提高录取率，以及是作为留信认证申请材料的一部分。埃默里大学成绩单能够体现您的的学习能力，包括埃默里大学课程成绩、专业能力、研究能力。（q微1954292140）具体来说，成绩报告单通常包含学生的学习技能与习惯、各科成绩以及老师评语等部分，因此，成绩单不仅是学生学术能力的证明，也是评估学生是否适合某个教育项目的重要依据！

最新版美国休斯顿大学毕业证（鲍贬毕业证书）原版定制Taqyea

鉴于此，定制休斯顿大学学位证书提升履历【q薇1954292140】原版高仿休斯顿大学毕业证(UH毕业证书)可先看成品样本【q薇1954292140】帮您解决在美国休斯顿大学未毕业难题，美国毕业证购买，美国文凭购买，【q微1954292140】美国文凭购买，美国文凭定制，美国文凭补办。专业在线定制美国大学文凭，定做美国本科文凭，【q微1954292140】复制美国The University of Houston completion letter。在线快速补办美国本科毕业证、硕士文凭证书，购买美国学位证、休斯顿大学Offer，美国大学文凭在线购买。如果您处于以下几种情况： ◇在校期间，因各种原因未能顺利毕业……拿不到官方毕业证 ◇面对父母的压力，希望尽快拿到； ◇不清楚认证流程以及材料该如何准备； ◇回国时间很长，忘记办理； ◇回国马上就要找工作，办给用人单位看； ◇企事业单位必须要求办理的 ◇需要报考公务员、购买免税车、落转户口 ◇申请留学生创业基金【复刻一套休斯顿大学毕业证成绩单信封等材料最强攻略,Buy The University of Houston Transcripts】购买日韩成绩单、英国大学成绩单、美国大学成绩单、澳洲大学成绩单、加拿大大学成绩单（q微1954292140）新加坡大学成绩单、新西兰大学成绩单、爱尔兰成绩单、西班牙成绩单、德国成绩单。成绩单的意义主要体现在证明学习能力、评估学术背景、展示综合素质、提高录取率，以及是作为留信认证申请材料的一部分。休斯顿大学成绩单能够体现您的的学习能力，包括休斯顿大学课程成绩、专业能力、研究能力。（q微1954292140）具体来说，成绩报告单通常包含学生的学习技能与习惯、各科成绩以及老师评语等部分，因此，成绩单不仅是学生学术能力的证明，也是评估学生是否适合某个教育项目的重要依据！

8queensproblemusingbacktracking-120903114053-phpapp01.pptxhalderdhrubo6

Current Affairs for Prelims (Schemes) 2024 (1).pptxmalavikasprinklr

Revolutionizing Environmental Compliance with AI.pdfDorian F Corliss

TECHNOLOGY LIVELIHOOD EDUCATIONLESSON EXEMPLARAnnMargrettDuka

Ruchi Bhatia HRGurukul - The Roads less travelledRuchi Bhatia

Biography and career history of Dr. Gabriel CarabelloDr. Gabriel Carabello

Power BI Jobs in Jaipur Your Gateway to Data Analytics Careers in Rajasthan.pptxvinay salarite

Cost_Effective_Multi_Source_Energy_Harvesting_System1 final 5.pptxAnishNaskar4

Presentation about the current products.pptxikrammustafa51

最新版意大利拉奎拉大学毕业证（础蚕鲍滨尝础毕业证书）原版定制taqyea

Rolph Balgobin - The Different Types of EntrepreneurRolph Balgobin

Gives a structured overview of the skills measured in the DP-700 examthehulk1299

PRESENTATION general mathematicsppt.pptxDharylBallarta

Pakistan Economy presentation pakistan.pptxikrammustafa51

Corporate Philanthropy’s Impact on Communities.pdfHassan Dakhlallah

Quarter 3 Program Implementation Review and Performance AssessmentLaMariaAngelicaPunay

Yellow and Purple Doodle Startup Pitch Deck Presentation.pdf.pdfkharadeshreya2210

IISc-CDS-v2xxxxxxxxxxxxxcxxxxxx (1).potxrnkaushal2

最新版美国埃默里大学毕业证（贰尘辞谤测毕业证书）原版定制Taqyea

最新版美国休斯顿大学毕业证（鲍贬毕业证书）原版定制Taqyea

8queensproblemusingbacktracking-120903114053-phpapp01.pptxhalderdhrubo6

Current Affairs for Prelims (Schemes) 2024 (1).pptxmalavikasprinklr

Dbms assignment 3(a) (1)

1. #DBMS assignment 3(a) #Author : Soumak Paul. #Create table: DEPARTMENT CREATE TABLE DEPARTMENT( DeptCode varchar(4) NOT NULL, DeptName varchar(40) NOT NULL, HOD varchar(4), PRIMARY KEY(DeptCode)); #Insert values into DEPARTMENT table. INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD) VALUES('IT','Information Technology','F201'); INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD) VALUES('BIO','Bio Technology','F901'); INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD) VALUES('CSE','Computer Science and Engineering,'F101'); INSERT INTO DEPARTMENT(DeptCode,DeptName,HOD) VALUES('NS','Natural Science','F506'); #Add a foreign key constraint in STUDENT against DeptCode column which references department. ALTER TABLE STUDENT ADD FOREIGN KEY (DeptCode) REFERENCES DEPARTMENT(DeptCode); #Create table: FACULTY CREATE TABLE FACULTY( FacultyCode varchar(4) NOT NULL, FacultyName varchar(15) NOT NULL, DateOfJoin date NOT NULL, DeptCode varchar(4) NOT NULL, PRIMARY KEY (FacultyCode)); #Insert values into FACULTY table. INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F101','M. Sinha','2005-01-01','CSE'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F105','P. Sarkar','2019-02-01','CSE'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F201','S. Mazumder','2005-09-15','IT'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F301','S. Mondal','2018-08-01','CSE'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F401','D. Majumdar','2003-12-01','IT'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F506','N. Biswas','2013-12-31','NS'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode)

2. VALUES('F607','R. Paul','2007-04-10','BIO'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F704','S. Sarkar','2012-01-01','IT'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F808','K. Das','2010-06-15','IT'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F901','R. Roy','2017-06-15','BIO'); INSERT INTO FACULTY(FacultyCode,FacultyName,DateOfJoin,DeptCode) VALUES('F902','R. Biswas','2018-06-15','BIO'); #Alter the table Faculty and add check constraint such that FacultyCode starts with ‘F’ ALTER TABLE FACULTY ADD CONSTRAINT FacultyCodeCheck CHECK (FacultyCode like 'F%'); #Alter the table Faculty and add check constraint such DeptCode is either CSE,IT, BIO,NS ALTER TABLE FACULTY ADD CONSTRAINT DeptCodeCheck CHECK (DeptCode IN ('CSE','IT','NS','BIO')); #Add constraint : DeptCode of Faculty is foreign key and references DeptCode in Department. ALTER TABLE FACULTY ADD FOREIGN KEY (DeptCode) REFERENCES DEPARTMENT(DeptCode); #Add Constraint: HOD of Department table is foreign key and references FacultyCode of Faculty. ALTER TABLE DEPARTMENT ADD FOREIGN KEY (HOD) REFERENCES FACULTY(FacultyCode); #Find the names of faculties of CSE Department. SELECT FacultyName FROM FACULTY WHERE DeptCode = 'CSE'; #Find the number of faculties in the IT department SELECT COUNT(*) FROM FACULTY WHERE DeptCode = 'IT'; #Find the number of faculties who joined in August?. SELECT COUNT(*) AS 'Hired in August' FROM FACULTY WHERE (MONTH(DateOfJoin) = 8); #Show the names of the heads of departments with department name. SELECT FACULTY.DeptCode AS 'DEPARTMENT NAME', FACULTY.FacultyName AS 'HOD NAME' FROM FACULTY INNER JOIN DEPARTMENT ON FACULTY.DeptCode = DEPARTMENT.DeptCode GROUP BY DEPARTMENT.DeptCode ORDER BY DEPARTMENT.DeptCode;

3. #Add an extra attribute to the faculty table - Salary Number(8,2) ALTER TABLE FACULTY ADD COLUMN SalaryNumber DECIMAL(8,2) AFTER DeptCode; #Insert values into the corresponding field Salary Number(8,2) (Enter distinct values). UPDATE FACULTY SET SalaryNumber = '25000.00' WHERE FacultyCode = 'F101'; UPDATE FACULTY SET SalaryNumber = '15000.00' WHERE FacultyCode = 'F105'; UPDATE FACULTY SET SalaryNumber = '18000.00' WHERE FacultyCode = 'F201'; UPDATE FACULTY SET SalaryNumber = '16500.00' WHERE FacultyCode = 'F301'; UPDATE FACULTY SET SalaryNumber = '16000.00' WHERE FacultyCode = 'F401'; UPDATE FACULTY SET SalaryNumber = '18000.00' WHERE FacultyCode = 'F506'; UPDATE FACULTY SET SalaryNumber = '15000.00' WHERE FacultyCode = 'F607'; UPDATE FACULTY SET SalaryNumber = '11000.00' WHERE FacultyCode = 'F704'; UPDATE FACULTY SET SalaryNumber = '13000.00' WHERE FacultyCode = 'F808'; UPDATE FACULTY SET SalaryNumber = '20000.00' WHERE FacultyCode = 'F901'; UPDATE FACULTY SET SalaryNumber = '14000.00' WHERE FacultyCode = 'F902'; #Find the Department having more than one faculty. SELECT DEPARTMENT.DeptCode FROM FACULTY INNER JOIN DEPARTMENT ON FACULTY.DeptCode = DEPARTMENT.DeptCode GROUP BY DEPARTMENT.DeptCode HAVING COUNT(*) > 1; #Find the name, department of the faculties who earn between 8000 and 12000. SELECT FacultyName, DeptCode FROM FACULTY WHERE (SalaryNumber >= 8000 AND SalaryNumber <= 12000); #Find the name of the department with maximum faculties. SELECT DeptName, COUNT(*) AS DEPTCOUNT FROM FACULTY JOIN DEPARTMENT ON FACULTY.DeptCode = DEPARTMENT.DeptCode GROUP BY DeptName ORDER BY DEPTCOUNT DESC LIMIT 1; #Find the senior-most faculty. SELECT * FROM FACULTY ORDER BY DateOfJoin LIMIT 1; #Find the name of the faculty who has completed 5 years?. SELECT FacultyName FROM FACULTY WHERE (DATEDIFF(CURDATE(),DateOfJoin) > 1826);

狠狠撸

Dbms assignment 3(a) (1)

Recommended

More Related Content

Similar to Dbms assignment 3(a) (1) (20)

Recently uploaded (20)

Dbms assignment 3(a) (1)