Mangodb student, employee

 Q1. Create a collection called Students with following fields

⮚ use Masters

switched to db Masters

⮚ db.createCollection('student')

{ ok: 1 }

⮚ db.student.insertMany([{usn:12345,name:'Preetham',course:'MBA',Grade:'A',major

Statics'},

…{usn:12346, name:'Berty pais',course:'MBA', major:'Statics',Grade:'A'},

…{usn:12350,course:'BCA', name:'Dhanraj major:'Frontend developer',Grade:'A'},

…{usn:12351,name:'Vishal',course:'BSc',major:'Maths',Grade:'A},

…{usn:12352,name:'Balaji',course:'BCA',major:'Big Data Analytic',Grade:'A+'},

…{usn:12353,name:'Yashas',course:'MCA',major:'Ethichal hacking',Grade:'B'},

... {usn:12560,name:'Sharath',course:'MCA',major:'Ethichal hacking',Grade:'A+'},

... {usn:12561,name:'Abc',course:'MCA',major:'Backend Developing',Grade:'A'}])

{

acknowledged: true,

insertedIds: {

'0': ObjectId("6548e35cdd3ed8b017401ce5"),

'1': ObjectId("6548e35cdd3ed8b017401ce6"),

'2': ObjectId("6548e35cdd3ed8b017401ce7"),

'3': ObjectId("6548e35cdd3ed8b017401ce8"),

'4': ObjectId("6548e35cdd3ed8b017401ce9"),

'5': ObjectId("6548e35cdd3ed8b017401cea"),

'6': ObjectId("6548e35cdd3ed8b017401ceb"),

'7': ObjectId("6548e35cdd3ed8b017401cec")

}

}


2. Read --- Querying data

⮚ db.student.find()

[

{

_id: ObjectId("6548e35cdd3ed8b017401ce5"),

usn: 12345,

name: 'Preetham',

course: 'MBA',

Grade: 'A',

major: 'Statics'

},

{

_id: ObjectId("6548e35cdd3ed8b017401ce6"),

usn: 12346,

name: 'Berty pais',

course: 'MBA',

major: 'Statics',

Grade: 'A'

},

{

_id: ObjectId("6548e35cdd3ed8b017401ce7"),

usn: 12350,

course: 'BCA',

name: 'Dhanraj',

major: 'Frontend developer',

Grade: 'A'

},

{

_id: ObjectId("6548e35cdd3ed8b017401ce8"),

usn: 12351,

name: 'Vishal',

course: 'BSc',

major: 'Maths',

Grade: 'A+'

},

{

_id: ObjectId("6548e35cdd3ed8b017401ce9"),

usn: 12352,

name: 'Yashas',

course: 'BCA',

major: 'Big Data Analytic',

Grade: 'A+'

},

{

_id: ObjectId("6548e35cdd3ed8b017401cea"),

usn: 12353,

name: 'Sharath',

course: 'MCA',

major: 'Ethichal hacking',

Grade: 'B'

},

{

_id: ObjectId("6548e35cdd3ed8b017401ceb"),

usn: 12560,

name: 'Abc',

course: 'MCA',

major: 'Ethichal hacking',

Grade: 'A+'

},

{

_id: ObjectId("6548e35cdd3ed8b017401cec"),

usn: 12561

name: 'Balaji',

course: 'MCA',

major: 'Backend Developing',

Grade: 'A'

}

]


3. Display All students Students of BCA

⮚ db.student.find({course:'BCA'}).forEach(function(doc){print('USN : '+doc.usn+'

NAME : '+doc.name+' COURSE : '+doc.course+' MAJOR : '+doc.major+' GRADE :

'+doc.Grade)})

USN : 12350 NAME : Dhanraj COURSE : BCA MAJOR : Frontend developer GRADE : A

USN : 12352 NAME : Yashas COURSE : BCA MAJOR : Big Data Analytic GRADE : A+


4. Display those who are under the course MCA

⮚ db.student.find({course:'MCA'}).forEach(function(doc){print('USN : '+doc.usn+'

NAME : '+doc.name+' COURSE : '+doc.course+' MAJOR : '+doc.major+' GRADE :

'+doc.Grade)})

USN : 12353 NAME : Abc COURSE : MCA MAJOR : Ethichal hacking GRADE : B

USN : 12560 NAME : Balaji COURSE : MCA MAJOR : Ethichal hacking GRADE : A+

USN : 12561 NAME : Sharath COURSE : MCA MAJOR : Backend Developing GRADE : A


5. Students with USN more than 12350

⮚ db.student.find({usn:{$gt:12350}}).forEach(function(doc){print('USN : '+doc.usn+'

NAME : '+doc.name)})

USN : 12351 NAME : Vishal

USN : 12352 NAME : Sharath

USN : 12353 NAME : Yashas

USN : 12560 NAME : Abc

USN : 12561 NAME : Balaji


6. Display the Students of MCA with major 'Big data'

db.student.find({major:'Big Data Analytic'}).forEach(function(doc){print('USN :

'+doc.usn+' NAME : '+doc.name)})

USN : 12352 NAME : Yashas

7. Display the Student’s Names starting with B

⮚ db.student.find({name:/^B/}).forEach(function(doc){print('USN : '+doc.usn+'

NAME : '+doc.name)})

USN : 12345 NAME : Berty pais





Employee program mangodb:

Employee Table :

1. First Insert 11 Records into the Employee Collection:

⮚ db.employees.insertMany( [ { eno: 1, fname: 'Abc', lname: 'g',salary:25000,

doj:'13-12-2022', gender:'F', designation:'seniour developer', dno:'d01', pno:'p01',

supereno:null},

...{ eno: 2, fname: 'Def', lname: 'D', salary:25000, doj:'13-12-2022', gender:'F',

designation:'data analyst', dno:'d02', pno:'p01', supereno:1},

...{ eno: 3, fname: 'Def', lname: null, salary:15000, gender:'F', doj:'23-01-2022',

designation:'data entrist', dno:'d02', pno:'p02', supereno:2},

...{ eno: 4, fname: 'Ghi', lname: null, salary:20000, doj:'23-10-2021', gender:'',

designation:'data entrist', pno:'p02', dno:'d02', supereno:2},

...{ eno: 5, fname: 'Ghi', lname: 'K', salary:20000, doj:'23-10-2021', gender:'M',

designation:'juniour developer', dno:'d01', pno:'p01', supereno:1},

...{ eno: 5, fname: 'Preetham', lname: 'N', salary:12000, gender:'M', doj:'12-04-2023',

designation:'clarck', dno:'d01', pno:'p01', supereno:1}])

{

acknowledged: true,

insertedIds: {

'0': ObjectId("653fafe876c93703c3cc668f"),

'1': ObjectId("653fafe876c93703c3cc6690"),

'2': ObjectId("653fafe876c93703c3cc6691"),

'3': ObjectId("653fafe876c93703c3cc6692"),

'4': ObjectId("653fafe876c93703c3cc6693"),

'5': ObjectId("653fafe876c93703c3cc6694")

}

}

⮚ db.employees.insertMany([{ eno: 7, fname: 'zxc', lname: 'ghale', salary: 55000, doj:

'13-12-2022', gender: 'M', designation: 'Manager', dno: 'd01', pno: 'p01', supereno:

null }, { eno: 8, fname: 'zxc', lname: 'ghale', salary: 40000, doj: '13-02-2023', gender:

'M', designation: 'data scientist', dno: 'd03', pno: 'p03', supereno: 2 }, { eno: 9, fname:

'qwe', lname: null, salary: 25000, gender: 'F', doj: '23-01-2021', designation: 'data

entrist', dno: 'd02', pno: 'p02', supereno: 2 }, { eno: 10, fname: 'Preetham', lname: 'n',

salary: 20000, doj: '03-11-2021', gender: 'M', designation: 'Manager', pno: 'p02', dno:

'd02', supereno: null }, { eno: 11, fname: 'Asdf', lname: 'kumar', salary: 35000, doj:

'23-10-2021', gender: 'M', designation: 'seniour developer', dno: 'd01', pno: 'p01',

supereno: 7 }, { eno: 12, fname: 'Jkl', lname: 'panchal', salary: 25000, gender: 'M',

doj: '12-04-2023', designation: 'juniour developer', dno: 'd01', pno: 'p01', supereno: 7

}])

{

acknowledged: true,

insertedIds: {

'0': ObjectId("653fb1a376c93703c3cc6695"),

'1': ObjectId("653fb1a376c93703c3cc6696"),

'2': ObjectId("653fb1a376c93703c3cc6697"),

'3': ObjectId("653fb1a376c93703c3cc6698"),

'4': ObjectId("653fb1a376c93703c3cc6699"),

'5': ObjectId("653fb1a376c93703c3cc669a")

}

}


1. Retrieve First_name,salary of all the employees whose designation is “Manager”

⮚ db.employees.find({designation:'Manager'})

[

{

_id: ObjectId("653fb1a376c93703c3cc6695"),

eno: 7,

fname: 'zxc',

lname: 'ghale',

salary: 55000,

doj: '13-12-2022',

gender: 'M',

designation: 'Manager',

dno: 'd01',

pno: 'p01',

supereno: null

},

{

_id: ObjectId("653fb1a376c93703c3cc6698"),

eno: 10,

fname: 'Preetham',

lname: 'n',

salary: 60000,

doj: '03-11-2021',

gender: 'M',

designation: 'Manager',

pno: 'p02',

dno: 'd02',

supereno: null

}

]

⮚ db.employees.find({designation:'Manager'}).forEach(function(doc){print('eno :

'+doc.eno+' name : '+doc.fname+' '+doc.lname+' designation : '+doc.designation)})

eno : 7 name : zxc ghale designation : Manager

eno : 10 name : Preetham n designation : Manager


2. Fetch the documents from a collection for only those employees whose salary is eitherdb.employees.find({

$or:[{salary:25000},{doj:'12-04-2023'}]})

[

{

_id: ObjectId("653fafe876c93703c3cc668f"),

eno: 1,

fname: 'Abc',

lname: 'g',

salary: 25000,

doj: '13-12-2022',

gender: 'F',

designation: 'seniour developer',

dno: 'd01',

pno: 'p01',

supereno: 7

},

{

_id: ObjectId("653fafe876c93703c3cc6690"),

eno: 2,

fname: 'Def',

lname: 'D',

salary: 25000,

doj: '13-12-2022',

gender: 'F',

designation: 'data analyst',

dno: 'd02',

pno: 'p01',

supereno: 10

},

{

_id: ObjectId("653fafe876c93703c3cc6694"),

eno: 6,

fname: 'Preetham',

lname: 'N',

salary: 12000,

gender: 'M',

doj: '12-04-2023',

designation: 'clarck',

dno: 'd01',

pno: 'p01',

supereno: 7

},

{

_id: ObjectId("653fb1a376c93703c3cc6697"),

eno: 9,

fname: 'qwe',

lname: '',

salary: 25000,

gender: 'F',

doj: '23-01-2021',

designation: 'data entrist',

dno: 'd02',

pno: 'p02',

supereno: 10

},

{

_id: ObjectId("653fb1a376c93703c3cc669a"),

eno: 12,

fname: 'Jkl',

lname: 'panchal',

salary: 25000,

gender: 'M',

doj: '12-04-2023',

designation: 'juniour developer',

dno: 'd01',

pno: 'p01',

supereno: 7

}

]

⮚ db.employees.find({ $or: [{ salary: 25000 }, { doj: '12-04-2023' }]

}).forEach(function(doc){print('eno : '+doc.eno+' |||| name : '+doc.fname+'

'+doc.lname+' |||| salary : '+doc.salary+' |||| doj : '+doc.doj)})

eno : 1 |||| name : Abc g |||| salary : 25000 |||| doj : 13-12-2022

eno : 2 |||| name : Def D |||| salary : 25000 |||| doj : 13-12-2022

eno : 6 |||| name : Preetham N |||| salary : 12000 |||| doj : 12-04-2023

eno : 9 |||| name : qwe |||| salary : 25000 |||| doj : 23-01-2021

eno : 12 |||| name : Jkl panchal |||| salary : 25000 |||| doj : 12-04-2023


3.Skip the first 5 doc of employee collection and the show next five docs

⮚ db.employees.find().skip(5).limit(5).forEach(function(doc){print('eno : '+doc.eno+'

|||| Name : '+doc.fname+' '+doc.lname+' |||| designation : '+doc.designation)})

eno : 6 |||| Name : Preetham N |||| designation : clarck

eno : 7 |||| Name : zxc ghale |||| designation : Manager

eno : 8 |||| Name : zxc ghale |||| designation : data scientist

eno : 9 |||| Name : qwe |||| designation : data entrist

eno : 10 |||| Name : Preetham n |||| designation : Manager


4. Sort the documents from the employee collection first on dept_no in ASC order and then

on salary in DESC Order

⮚ db.employees.aggregate([{$sort:{dno:1,salary:-1}}]).forEach(function(doc){print('en

o : '+doc.eno+' |||| Name : '+doc.fname+' '+doc.lname+' |||| dep_no : '+doc.dno+' ||||

salary : '+doc.salary)})


eno : 7 |||| Name : zxc ghale |||| dep_no : d01 |||| salary : 55000

eno : 11 |||| Name : Asdf kumar |||| dep_no : d01 |||| salary : 35000

eno : 1 |||| Name : Abc g |||| dep_no : d01 |||| salary : 25000

eno : 12 |||| Name : Jkl panchal |||| dep_no : d01 |||| salary : 25000

eno : 5 |||| Name : Ghi K |||| dep_no : d01 |||| salary : 20000

eno : 6 |||| Name : Preetham N |||| dep_no : d01 |||| salary : 12000

eno : 10 |||| Name : Preetham n |||| dep_no : d02 |||| salary : 60000

eno : 2 |||| Name : Def D |||| dep_no : d02 |||| salary : 25000

eno : 9 |||| Name : qwe |||| dep_no : d02 |||| salary : 25000

eno : 4 |||| Name : Ghi |||| dep_no : d02 |||| salary : 20000

eno : 3 |||| Name : Def |||| dep_no : d02 |||| salary : 15000

eno : 8 |||| Name : zxc ghale |||| dep_no : d03 |||| salary : 40000


5. To retrive the first 3 documents from emp colletionwherin dept no D01

⮚ db.employees.find({dno:"d01"}).limit(3).forEach(function(doc){print('eno :

'+doc.eno+' |||| Name : '+doc.fname+' '+doc.lname+' |||| dep_no : '+doc.dno)})

eno : 1 |||| Name : Abc g |||| dep_no : d01

eno : 5 |||| Name : Ghi K |||| dep_no : d01

eno : 6 |||| Name : Preetham N |||| dep_no : d01


6. Find the doc from the employee collection where emp name ends with ‘a’

⮚ db.employees.find({fname:{$regex:"a$"}}).forEach(function(doc){print('Name :

'+doc.fname)})

Name : Abc

Name : Def

Name : Def

Name : qwe


8. Retrieve the name of all the employees who involved in project either P01 or P02

⮚ db.employees.find({pno:'p02'}).forEach(function(doc){print('Name : '+doc.fname)})

Name : Def

Name : Ghi

Name : qwe

Name : Preetham

⮚ db.employees.find({$or:[{pno:'p02'}]},{fname:1})

[

{ _id: ObjectId("653fafe876c93703c3cc6691"), fname: 'Def' },

{ _id: ObjectId("653fafe876c93703c3cc6692"), fname: 'Ghi' },

{ _id: ObjectId("653fb1a376c93703c3cc6697"), fname: 'qwe' },

{ _id: ObjectId("653fb1a376c93703c3cc6698"), fname: 'Preetham' }

]


10. Find the Maximum and Minimum Salary paid in each department

⮚ db.employees.aggregate([{$group:

{

_id:"$dno",

max_salary:{$max : "$salary"},

min_salary:{$min:"$salary"}

}}])

[

{ _id: 'd01', max_salary: 55000, min_salary: 12000 },

{ _id: 'd02', max_salary: 60000, min_salary: 15000 },

{ _id: 'd03', max_salary: 40000, min_salary: 40000 }

]




Comments

Post a Comment

Popular posts from this blog

Cassandra all programs

 1 question -Here's how you can perform these tasks in SQL: ### 1. Create the `Student_info` Table ```sql CREATE TABLE Student_info (     rollno INT PRIMARY KEY,     studname VARCHAR(50),     age INT,     department VARCHAR(50),     lastexampercent DECIMAL(5, 2) ); ``` ### 2. Insert Records ```sql INSERT INTO Student_info (rollno, studname, age, department, lastexampercent) VALUES (1, 'John', 20, 'Computer Science', 85.5), (2, 'Alice', 22, 'Mathematics', 90.0), (3, 'Bob', 21, 'Physics', 78.5); ``` ### 3. View Records of Students with Roll Numbers 1, 2, and 3 ```sql SELECT * FROM Student_info WHERE rollno IN (1, 2, 3); ``` ### 4. Create an Index on the `studname` Column ```sql CREATE INDEX idx_studname ON Student_info (studname); ``` ### 5. Display the Records with a Limit of 2 Results ```sql SELECT * FROM Student_info LIMIT 2; ``` ### 6. Add a New Column `address` to the `Student_info` Table ```sql ALTER TABLE Student_info ADD addre...
Read more

Mangodb games , movies

Games :- A. Create a collection called ‘games’. We’re going to put some games in it. Creation - db.createCollection('games'); B. Add 5 games to the database. Give each document the following properties: name, genre, rating (out of 100) If you make some mistakes and want to clean it out, use remove()on your collection. Insertion - db.games.insertMany([  {  name: "The Legend of Zelda: Breath of the Wild",  genre: "Action-Adventure",  rating: 97,  achievements: {}  },  {  name: "Hollow Knight",  genre: "Metroidvania",  rating: 90,  achievements: {}  },  {  name: "Celeste",  genre: "Platformer",  rating: 94,  achievements: {}  },  {  name: "God of War",  genre: "Action",  rating: 93,  achievements: {}  },  {  name: "Stardew Valley",  genre: "Simulation",  rating: 89, achievements: {}  } ]); C. Write a query that returns all the games  db.ga...
Read more
 CREATE KEYSPACE keyspace_name  WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 }  AND durable_writes = true;
Read more