React Js - Mini Project || Fresco Play || 62042

In this application, do the following :
  • Create a signup page to register the user.
  • Create a sign-in page to login to the application if the credentials are correct.
  • Create components to Register, Edit, and View patient details.
  • Create components to Book, Edit, and View the appointments.
  • Create a Navbar.

Signup Form


import React, { Component } from "react";
import { NavLink } from "react-router-dom";
import DatePicker from "react-datepicker";
import { adminDetailsData } from "./data.js";

import "../App.css";
class SignUpForm extends Component {
    constructor(props) {
        super(props);

        this.state = {
            uname: "",
            email: "",
            password: "",
            dob: "",
            mobileno: "",
            location: "",
        };

        this.handleChange = this.handleChange.bind(this);
        this.handleSubmit = this.handleSubmit.bind(this);
    }

    handleChange(e) {
        let target = e.target;
        let value = target.type === "checkbox" ? target.checked : target.value;
        let name = target.name;

        this.setState({
            [name]: value,
        });
    }

    handleSubmit(e) {
        e.preventDefault();

        if (this.canBeSubmitted()) {
            adminDetailsData.add(
                this.state.uname,
                this.state.email,
                this.state.password,
                this.state.dob,
                this.state.mobileno,
                this.state.location
            );
            this.setState({ name: e.target.value });
            this.props.history.push("/sign-in");
        }
    }
    canBeSubmitted() {
        const { uname, email, password, dob, mobileno, location } = this.state;
        return (
            uname.length > 4 &&
            email.length > 4 &&
            password.length > 4 &&
            dob.length > 4 &&
            mobileno.length > 4 &&
            location.length > 4
        );
    }

    render() {
        return (
            <div>
                <div>
                    <h3 style={{ textAlign: "center", paddingBottom: "10px" }}>
                        Digital Medical Record Database
                    </h3>
                </div>
                <div className="FormCenter">
                    <div className="FormTitle">
                        <NavLink to="/sign-in" className="FormTitle__Link">
                            Login
                        </NavLink>{" "}
                        or
                        <NavLink exact to="/" className="FormTitle__Link">
                            Register
                        </NavLink>
                    </div>

                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/*Write code here to create uname, email, dob, location, mobileno labels and inputs */}
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="uname">
                                Username
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="uname"
                                id="uname"
                                className="FormField__Input"
                                placeholder="Enter your username"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="email">
                                E-mail ID
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="email"
                                id="email"
                                className="FormField__Input"
                                placeholder="Enter your email"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="password"
                            >
                                Password
                            </label>
                            <input
                                type="password"
                                onChange={(e) => this.handleChange(e)}
                                name="password"
                                id="password"
                                className="FormField__Input"
                                placeholder="Enter your password"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="dob">
                                Date of Birth
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="dob"
                                id="dob"
                                className="FormField__Input"
                                placeholder="Enter your date of birth"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="mobileno"
                            >
                                Mobile No
                            </label>
                            <input
                                type="number"
                                onChange={(e) => this.handleChange(e)}
                                name="mobileno"
                                id="mobileno"
                                className="FormField__Input"
                                placeholder="Enter your mobile number"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="location"
                            >
                                Location
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="location"
                                id="location"
                                className="FormField__Input"
                                placeholder="Enter your mobile number"
                            />
                        </div>
                        <div className="FormField">
                            {/* Write code here to create Register Button */}
                            <button type="submit" className="FormField__Button">
                                Register
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default SignUpForm;

Signin Form


import React, { Component } from "react";
import { NavLink } from "react-router-dom";
import { adminDetailsData } from "./data.js";
import "../App.css";
import { Input } from "reactstrap";

class SignInForm extends Component {
    constructor() {
        super();

        this.state = {
            email: "",
            password: "",
            adminDetails: adminDetailsData.getData(),
        };

        this.handleChange = this.handleChange.bind(this);
        this.handleSubmit = this.handleSubmit.bind(this);
    }

    handleChange(e) {
        let target = e.target;
        let value = target.type === "checkbox" ? target.checked : target.value;
        let name = target.name;

        this.setState({
            [name]: value,
        });
    }

    handleSubmit(e) {
        e.preventDefault();
        e.stopPropagation();

        const { adminDetails } = this.state;

        let validCredentials = false;
        if (this.canBeSubmitted()) {
            adminDetails.map((admin) => {
                if (
                    this.state.email === admin.email &&
                    this.state.password === admin.password
                ) {
                    let currentUser = admin.adminId;
                    validCredentials = true;
                    adminDetailsData.setCurrentUser(admin.adminId);
                    this.props.history.push("/allpatients");
                    return;
                }
            });
            if (!validCredentials) {
                alert("please enter valid credentials");
                this.props.history.push("/sign-in");
            }
        }
    }
    canBeSubmitted() {
        const { email, password, adminDetails } = this.state;
        return email.length > 0 && password.length > 0;
    }

    render() {
        const isEnabled = this.canBeSubmitted();

        return (
            <div>
                <div>
                    <h3 style={{ textAlign: "center", paddingBottom: "10px" }}>
                        Digital Medical Record Database
                    </h3>
                </div>
                <div className="FormCenter">
                    <div className="FormTitle">
                        <NavLink to="/sign-in" className="FormTitle__Link">
                            Login
                        </NavLink>{" "}
                        or
                        <NavLink exact to="/" className="FormTitle__Link">
                            Register
                        </NavLink>
                    </div>

                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/*Write code here to create labels and fields for username and password */}
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="email">
                                E-Mail Address
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="email"
                                id="email"
                                className="FormField__Input"
                                placeholder="Enter your email"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="password"
                            >
                                Password
                            </label>
                            <input
                                type="password"
                                onChange={(e) => this.handleChange(e)}
                                name="password"
                                id="password"
                                className="FormField__Input"
                                placeholder="Enter your password"
                            />
                        </div>
                        {/* Write code here to create a login button */}
                        <div className="FormField">
                            <button type="submit" className="FormField__Button">
                                Login
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default SignInForm;

NavBar.jsx


import React, { useState } from "react";
import Medilogo from "../images/Medi-Logo.png";
import {
    Collapse,
    Navbar,
    NavbarToggler,
    NavbarBrand,
    Nav,
    NavItem,
    // NavLink,
    UncontrolledDropdown,
    DropdownToggle,
    DropdownMenu,
    DropdownItem,
} from "reactstrap";
import { Link } from "react-router-dom";
import { NavLink } from "react-router-dom";

const Example = (props) => {
    const [isOpen, setIsOpen] = useState(false);

    const toggle = () => setIsOpen(!isOpen);

    return (
        <React.Fragment>
            {/*should have a Navbar brand, toggler and the NavItem (logout) should be linked to sign-in page */}
            <Navbar color="light" light expand="md">
                <NavbarBrand href="/">DWRD</NavbarBrand>
                <NavbarToggler onClick={toggle} />
                <Collapse className="ml-auto" isOpen={isOpen} navbar>
                    <Nav className="mr-auto" navbar>
                        <NavItem>
                            <NavLink className="nav-link" to="/addPatient">
                                Add Patient
                            </NavLink>
                        </NavItem>
                        <NavItem>
                            <NavLink className="nav-link" to="/allPatients">
                                All Patients
                            </NavLink>
                        </NavItem>
                        <NavItem>
                            <NavLink className="nav-link" to="/bookAppointment">
                                Book Appointment
                            </NavLink>
                        </NavItem>
                        <NavItem>
                            <NavLink className="nav-link" to="/allAppointments">
                                All Appointments
                            </NavLink>
                        </NavItem>
                        <UncontrolledDropdown>
                            <DropdownToggle caret nav>
                                User
                            </DropdownToggle>
                            <DropdownMenu className="DropDowns">
                                <DropdownItem className="dropdDownItem">
                                    <NavLink to="/viewProfile">
                                        View Profile
                                    </NavLink>
                                </DropdownItem>
                                <DropdownItem className="dropdDownItem">
                                    <NavLink to="/">Logout</NavLink>
                                </DropdownItem>
                            </DropdownMenu>
                        </UncontrolledDropdown>
                    </Nav>
                </Collapse>
            </Navbar>
        </React.Fragment>
    );
};
export default Example;

AddPatient.jsx


import React, { Component } from "react";
import { NavLink } from "react-router-dom";
import NavBar from "./NavBar.jsx";
import "../App.css";
import { patientDetailsData } from "./data.js";

class AddPatient extends Component {
    constructor(props) {
        super(props);

        this.state = {
            name: "",
            email: "",
            dob: "",
            location: "",
            mobile: "",
        };

        this.handleChange = this.handleChange.bind(this);
        this.handleSubmit = this.handleSubmit.bind(this);
        this.handleCancel = this.handleCancel.bind(this);
    }

    handleChange(e) {
        let target = e.target;
        let value = target.type === "checkbox" ? target.checked : target.value;
        let name = target.name;

        this.setState({
            [name]: value,
        });
    }

    handleSubmit(e) {
        if (this.canBeSubmitted()) {
            alert("Patient Added successfully");
            patientDetailsData.add(
                this.state.name,
                this.state.email,
                this.state.dob,
                this.state.location,
                this.state.mobile
            );
            this.props.history.push("/allPatients");
        }
    }
    handleCancel(e) {
        e.preventDefault();
        this.props.history.push("/allPatients");
    }
    canBeSubmitted() {
        const { name, email, dob, location, mobile } = this.state;
        return (
            name.length > 0 &&
            email.length > 0 &&
            dob.length > 0 &&
            location.length > 0 &&
            mobile.length > 0
        );
    }

    render() {
        const isEnabled = this.canBeSubmitted();
        const name = this.state.name;
        const date = new Date();
        return (
            <div>
                <NavBar />
                <div>
                    <p
                        style={{
                            textAlign: "center",
                            paddingBottom: "10px",
                            paddingTop: "30px",
                            fontSize: "2em",
                        }}
                    >
                        Adding a Patient
                    </p>
                </div>
                {/* Write code here to create fields and input labels for name,email,dob,mobileno and location  */}
                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        <div></div>
                        <div className="FormField">
                            <label
                                className="FormField__ViewLabel"
                                htmlFor="name"
                            >
                                Name
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="name"
                                id="name"
                                className="FormField__Input"
                                placeholder="Enter your name"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="email">
                                E-mail ID
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="email"
                                id="email"
                                className="FormField__Input"
                                placeholder="Enter your email"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="dob">
                                Date of Birth
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="dob"
                                id="dob"
                                className="FormField__Input"
                                placeholder="Enter your date of birth"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="location"
                            >
                                Location
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="location"
                                id="location"
                                className="FormField__Input"
                                placeholder="Enter your mobile number"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="mobile"
                            >
                                Mobile
                            </label>
                            <input
                                type="number"
                                onChange={(e) => this.handleChange(e)}
                                name="mobile"
                                id="mobile"
                                className="FormField__Input"
                                placeholder="Enter your mobile number"
                            />
                        </div>
                        <div
                            className="FormField"
                            style={{
                                display: "flex",
                                justifyContent: "center",
                                gap: "40px",
                            }}
                        >
                            <button type="submit" className="FormField__Button">
                                Register
                            </button>
                            <button
                                type="button"
                                onClick={() => {
                                    this.props.history.push(`/allPatients`);
                                }}
                                className="FormField__Button"
                            >
                                Cancel
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default AddPatient;

All Patients.jsx


import React, { Component } from "react";
import NavBar from "./NavBar.jsx";
import "../App.css";
import { patientDetailsData } from "./data.js";
import { ListGroup, ListGroupItem } from "reactstrap";
class AllPatients extends Component {
    constructor(props) {
        super(props);
        this.state = {
            //Write function to get the data of patients with the name as appointmentsList:
            patientsList: patientDetailsData.getData(),
        };
        this.handleView = this.handleView.bind(this);
        this.handleEdit = this.handleEdit.bind(this);
        this.handleDelete = this.handleDelete.bind(this);
    }
    handleView(id) {
        this.props.history.push(`/viewPatient/${id}`);
    }
    handleEdit(id) {
        this.props.history.push(`/editPatient/${id}`);
    }
    handleDelete(e) {
        patientDetailsData.deletePatient(e);
        this.setState({
            patientsList: patientDetailsData.getData(),
        });
    }

    render() {
        const { patientsList } = this.state;
        const patientItems = patientsList.map((e, i) => (
            <ListGroupItem key={i}>
                {e.name}
                <button
                    className="FormField__all__Button"
                    onClick={() => {
                        this.handleEdit(i);
                    }}
                >
                    Edit
                </button>
                <button
                    className="FormField__all__Button"
                    onClick={() => {
                        this.handleView(i);
                    }}
                >
                    View
                </button>
            </ListGroupItem>
        ));
        return (
            <div style={{ height: "100%" }}>
                <NavBar />
                <form
                    style={{
                        display: "flex",
                        height: "100%",
                        alignItems: "center",
                    }}
                >
                    {patientsList.length === 0 ? (
                        <h1 style={{ textAlign: "center", flexGrow: "1" }}>
                            No Patients Found
                        </h1>
                    ) : (
                        <ListGroup
                            style={{ alignSelf: "flex-start", flexGrow: "1" }}
                        >
                            {patientItems}
                        </ListGroup>
                    )}
                </form>
            </div>
        );
    }
}

export default AllPatients;

AllAppointments.jsx


import React, { Component } from "react";
import NavBar from "./NavBar.jsx";
import "../App.css";
import AddPatient from "./AddPatient.jsx";
import { appDetailsData } from "./data";
import { ListGroupItem, ListGroup } from "reactstrap";

class AllAppointments extends Component {
    constructor() {
        super();
        this.state = {
            //Write function to get the appointment details with the name as appointmentsList:
            appointmentsList: appDetailsData.getData(),
        };
        this.handleView = this.handleView.bind(this);
        this.handleEdit = this.handleEdit.bind(this);
        this.handleDelete = this.handleDelete.bind(this);
    }
    handleView(appId) {
        this.props.history.push(`/viewAppointment/${appId}`);
    }
    handleEdit(appId) {
        this.props.history.push(`/editAppointment/${appId}`);
    }
    handleDelete(appId) {
        appDetailsData.deleteAppointment(appId);
        this.setState({
            appointmentsList: appDetailsData.getData(),
        });
    }
    appsList() {
        if (this.state.appointmentsList.length == 0)
            return <h1>No Appointments Found</h1>;
    }
    render() {
        const { appointmentsList } = this.state;

        return (
            <div style={{ height: "100%" }}>
                <NavBar />

                <form
                    style={{
                        display: "flex",
                        height: "100%",
                        alignItems: "center",
                    }}
                >
                    {appointmentsList.length === 0 ? (
                        <h1 style={{ textAlign: "center", flexGrow: "1" }}>
                            No Appoinments Found
                        </h1>
                    ) : (
                        <div style={{ height: "100%", width: "100%" }}>
                            <div>
                                <p
                                    style={{
                                        textAlign: "center",
                                        paddingBottom: "10px",
                                        paddingTop: "10px",
                                        fontSize: "2em",
                                        color: "Slate Blue",
                                    }}
                                >
                                    List of All Appointments
                                </p>
                            </div>
                            <ListGroup>
                                {appointmentsList.map((appointment, index) => (
                                    <ListGroupItem key={index}>
                                        {appointment.name}, Slot-
                                        {appointment.slot}
                                        <button
                                            className="FormField__all__Button"
                                            onClick={() => {
                                                this.handleEdit(
                                                    appointment.appId
                                                );
                                            }}
                                        >
                                            Edit
                                        </button>
                                        <button
                                            className="FormField__all__Button"
                                            onClick={() => {
                                                this.handleView(
                                                    appointment.appId
                                                );
                                            }}
                                        >
                                            View
                                        </button>
                                        <button
                                            className="FormField__all__Button"
                                            onClick={() => {
                                                this.handleDelete(
                                                    appointment.appId
                                                );
                                            }}
                                        >
                                            Delete
                                        </button>
                                    </ListGroupItem>
                                ))}
                            </ListGroup>
                        </div>
                    )}
                </form>
            </div>
        );
    }
}

export default AllAppointments;

BookAppointment.jsx


import React, { Component } from "react";
import NavBar from "./NavBar.jsx";
import "../App.css";
import { appDetailsData } from "./data";
import { patientDetailsData } from "./data";

class BookAppointment extends Component {
    constructor() {
        super();
        this.state = {
            name: "",
            disease: "",
            appdate: "",
            slot: "",
            description: "",
        };
        this.handleChange = this.handleChange.bind(this);
        this.handleSubmit = this.handleSubmit.bind(this);
        this.handleCancel = this.handleCancel.bind(this);
        this.handleDropdownChange = this.handleDropdownChange.bind(this);
        this.handleDropdownNameChange =
            this.handleDropdownNameChange.bind(this);
    }

    handleChange(e) {
        let target = e.target;
        let value = target.type === "checkbox" ? target.checked : target.value;
        let name = target.name;

        this.setState({
            [name]: value,
        });
    }
    handleDropdownChange(e) {
        if (e.target.value === "N/A")
            alert("please select slot other than N/A");
        this.setState({ slot: e.target.value });
    }
    handleDropdownNameChange(e) {
        this.setState({ name: e.target.value });
    }

    handleSubmit(e) {
        if (this.canBeSubmitted()) {
            e.preventDefault();

            let slot = this.slots.value;
            if (slot === "N/A" || this.state.name === "N/A") {
                alert("Please select slot and name values other than N/A");
            } else {
                alert("Appointment booked successfully");
                appDetailsData.add(
                    this.state.name,
                    this.state.disease,
                    this.state.appdate,
                    slot,
                    this.state.description
                );
                this.props.history.push("/allAppointments");
            }
        }
    }
    handleCancel(e) {
        this.props.history.push("/allAppointments");
    }

    canBeSubmitted() {
        const { name, disease, appdate, slot, description } = this.state;
        return (
            name.length > 4 &&
            disease.length > 0 &&
            appdate.length > 0 &&
            description.length > 0
        );
    }
    render() {
        const names = patientDetailsData.getName();

        const isEnabled = this.canBeSubmitted();
        const date = new Date();

        return (
            <div>
                <NavBar />
                <div>
                    <p
                        style={{
                            textAlign: "center",
                            paddingBottom: "10px",
                            paddingTop: "30px",
                            fontSize: "2em",
                        }}
                    >
                        Booking Appointment
                    </p>
                </div>
                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        <div></div>
                        <div className="FormField">
                            {/*Write code here to create dropdown to list the name of patients, if no patients are avilable then it should be N/A */}
                            <label className="FormField__Label" htmlFor="name">
                                Name of the Patient
                            </label>
                            <select name="name" className="DropDowns">
                                <option value="N/A">N/A</option>
                                {names.length > 0
                                    ? names.map((name, i) => (
                                            <option value={name} key={i}>
                                                name
                                            </option>
                                        ))
                                    : ""}
                            </select>
                        </div>
                        {/*Write code here to create date and disease labels */}

                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="disease"
                            >
                                Disease
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="disease"
                                id="disease"
                                className="FormField__Input"
                                placeholder="Enter your disease"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="appdate"
                            >
                                Date
                            </label>
                            <input
                                type="date"
                                onChange={(e) => this.handleChange(e)}
                                name="appdate"
                                id="appdate"
                                className="FormField__Input"
                                placeholder="Enter your appointment date"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="slot">
                                Slots
                            </label>
                            <select
                                name="slot"
                                class="DropDowns"
                                ref={(input) => (this.slots = input)}
                            >
                                <header>select slots </header>
                                <option value="N/A">N/A</option>
                                <option value="10-11 AM">10-11 AM</option>
                                <option value="1-2 PM">1-2 PM</option>
                                <option value="3-4 PM">3-4 PM</option>
                                <option value="6-8 PM">6-8 PM</option>
                            </select>
                        </div>
                        {/* Write code here to create description field,submit and cancel buttons */}

                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="description"
                            >
                                Disease
                            </label>
                            <input
                                type="text"
                                onChange={(e) => this.handleChange(e)}
                                name="description"
                                id="description"
                                className="FormField__Input"
                                placeholder="Enter your description"
                            />
                        </div>
                        <div
                            className="FormField"
                            style={{
                                display: "flex",
                                justifyContent: "center",
                                gap: "40px",
                            }}
                        >
                            <button type="submit" className="FormField__Button">
                                Book Now
                            </button>
                            <button
                                type="button"
                                onClick={() => {
                                    this.handleCancel();
                                }}
                                className="FormField__Button"
                            >
                                Cancel
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default BookAppointment;

ViewPatient.jsx


import React, { Component } from "react";
import NavBar from "./NavBar";
import { patientDetailsData } from "./data.js";

class ViewPatient extends Component {
    constructor(props) {
        super(props);
        this.state = {
            patient: patientDetailsData.viewPatientDetails(
                props.match.params.id
            ),
        };

        this.handleClose = this.handleClose.bind(this);
    }

    handleClose(e) {
        this.props.history.push("/allPatients");
    }

    render() {
        const { patient } = this.state;
        if (!patient) {
            return <h1>No patients found</h1>;
        }
        return (
            <div>
                <NavBar />
                <div>
                    <p
                        style={{
                            textAlign: "center",
                            paddingBottom: "10px",
                            paddingTop: "10px",
                            fontSize: "2em",
                        }}
                    >
                        Viewing Patient
                    </p>
                </div>
                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/* Write code here to create fields for name, disease,appdate, slot and mobile*/}
                        <div className="FormField">
                            <span className="FormField__Label" id="name">
                                Name - {patient.name}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="email">
                                E-mail ID - {patient.email}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="dob">
                                Date of Birth - {patient.dob}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="location">
                                Location - {patient.location}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="mobile">
                                Mobile - {patient.mobile}
                            </span>
                        </div>
                        <div className="FormField">
                            {/*Write code here to create close button */}
                            <button
                                type="button"
                                onClick={() => {
                                    this.handleClose();
                                }}
                                className="FormField__Button"
                            >
                                Close
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}
export default ViewPatient;

ViewProfile.jsx


import React, { Component } from "react";
import { NavLink } from "react-router-dom";
import NavBar from "./NavBar.jsx";
import { adminDetailsData } from "./data.js";
import "../App.css";

class ViewProfile extends Component {
    constructor(props) {
        super(props);
        this.state = {
            admin: adminDetailsData.getCurrentUser() || {},
        };
        this.handleClose = this.handleClose.bind(this);
    }
    handleClose(e) {
        e.preventDefault();
        this.props.history.push("/allPatients");
    }

    render() {
        const { admin } = this.state;
        return (
            <div>
                <NavBar />
                <div>
                    <h3 style={{ textAlign: "center", paddingBottom: "10px" }}>
                        Here are your details
                    </h3>
                </div>

                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/*Write code to create labels for name,email,dob,mobileno and location */}
                        <div className="FormField">
                            <span className="FormField__Label" id="name">
                                Username - {admin.uname}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="email">
                                E-mail ID - {admin.email}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="dob">
                                Date of Birth - {admin.dob}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="location">
                                Location - {admin.location}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="mobileno">
                                Mobile - {admin.mobileno}
                            </span>
                        </div>
                        <div className="FormField">
                            {/*Write code here to create a close button */}
                            <button
                                type="button"
                                onClick={() => {
                                    this.handleClose();
                                }}
                                className="FormField__Button"
                            >
                                Close
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default ViewProfile;

ViewAppointment.jsx


import React, { Component } from "react";
import NavBar from "./NavBar";
import { appDetailsData } from "./data.js";

class ViewAppointment extends Component {
    constructor(props) {
        super(props);
        this.state = {
            appointment: appDetailsData.getAppointmentDetails(
                props.match.params.appId
            ),
        };

        this.handleClose = this.handleClose.bind(this);
    }

    handleClose(e) {
        this.props.history.push("/allAppointments");
    }

    render() {
        const { appointment } = this.state;
        if (!appointment) {
            return <h1>No appointments found</h1>;
        }
        return (
            <div>
                <NavBar />
                <div>
                    <div>
                        <p
                            style={{
                                textAlign: "center",
                                paddingBottom: "10px",
                                paddingTop: "30px",
                                fontSize: "2em",
                            }}
                        >
                            Viewing Appointment
                        </p>
                    </div>
                </div>
                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/* Write code here to display name, appdate, slot, description and disease */}
                        <div className="FormField">
                            {/*Write code here to create dropdown to list the name of patients, if no patients are avilable then it should be N/A */}
                            <span className="FormField__Label" id="name">
                                Name of the Patient - {appointment.name}
                            </span>
                        </div>
                        {/*Write code here to create date and disease labels */}

                        <div className="FormField">
                            <span className="FormField__Label" id="disease">
                                Disease - {appointment.disease}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="appdate">
                                Date - {appointment.appdate}
                            </span>
                        </div>
                        <div className="FormField">
                            <span className="FormField__Label" id="slot">
                                Slot - {appointment.slot}
                            </span>
                        </div>

                        <div className="FormField">
                            <span className="FormField__Label" id="description">
                                Disease - {appointment.description}
                            </span>
                        </div>
                        <div className="FormField">
                            {/*Write code here to create a close button */}
                            <button
                                type="button"
                                onClick={() => {
                                    this.handleClose();
                                }}
                                className="FormField__Button"
                            >
                                Close
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default ViewAppointment;

EditPatient.jsx


import React, { Component } from "react";
import "../App.css";
import NavBar from "./NavBar.jsx";
import { patientDetailsData } from "./data.js";
import { Route } from "react-router-dom";

class EditPatient extends Component {
    constructor(props) {
        super(props);
        const patient =
            patientDetailsData.getPatientDetails(props.match.params.id) ||
            undefined;
        this.state = {
            name: patient.name || "",
            email: patient.email || "",
            dob: patient.dob || "",
            location: patient.location || "",
            mobile: patient.mobile || "",
            patient: patient,
        };
        this.handleSubmit = this.handleSubmit.bind(this);
        this.handleCancel = this.handleCancel.bind(this);
        this.handleChange = this.handleChange.bind(this);
    }
    handleSubmit(e) {
        if (this.canBeSubmitted()) {
            e.preventDefault();

            patientDetailsData.edit(
                this.state.patient.id,
                this.state.name,
                this.state.email,
                this.state.dob,
                this.state.location,
                this.state.mobile
            );

            this.props.history.push("/allPatients");
        }
    }
    canBeSubmitted() {
        const { name, email, dob, location, mobile } = this.state;
        return (
            name.length > 0 &&
            email.length > 0 &&
            dob.length > 0 &&
            location.length > 0 &&
            mobile.length > 0
        );
    }
    handleCancel(e) {
        this.props.history.push("/allPatients");
    }
    handleChange(e) {
        let target = e.target;
        let value = target.type === "checkbox" ? target.checked : target.value;
        let name = target.name;

        this.setState({
            [name]: value,
        });
    }
    render() {
        const { patient } = this.state;
        if (!patient) {
            return <div>Patient doesnot exist</div>;
        }

        return (
            <div>
                <NavBar />
                <div>
                    <p
                        style={{
                            textAlign: "center",
                            paddingBottom: "10px",
                            paddingTop: "10px",
                            fontSize: "2em",
                        }}
                    >
                        Edit patient
                    </p>
                </div>
                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/* Write code here to create labels and input fields for edit patient like name,email,dob,location and mobile*/}
                        <div></div>
                        <div className="FormField">
                            <label
                                className="FormField__ViewLabel"
                                htmlFor="name"
                            >
                                Name
                            </label>
                            <input
                                type="text"
                                value={patient.name}
                                onChange={(e) => this.handleChange(e)}
                                name="name"
                                id="name"
                                className="FormField__Input"
                                placeholder="Enter your name"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="email">
                                E-mail ID
                            </label>
                            <input
                                type="text"
                                value={patient.email}
                                onChange={(e) => this.handleChange(e)}
                                name="email"
                                id="email"
                                className="FormField__Input"
                                placeholder="Enter your email"
                            />
                        </div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="dob">
                                Date of Birth
                            </label>
                            <input
                                type="text"
                                value={patient.dob}
                                onChange={(e) => this.handleChange(e)}
                                name="dob"
                                id="dob"
                                className="FormField__Input"
                                placeholder="Enter your date of birth"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="location"
                            >
                                Location
                            </label>
                            <input
                                type="text"
                                value={patient.location}
                                onChange={(e) => this.handleChange(e)}
                                name="location"
                                id="location"
                                className="FormField__Input"
                                placeholder="Enter your mobile number"
                            />
                        </div>
                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="mobile"
                            >
                                Mobile
                            </label>
                            <input
                                type="number"
                                value={patient.mobile}
                                onChange={(e) => this.handleChange(e)}
                                name="mobile"
                                id="mobile"
                                className="FormField__Input"
                                placeholder="Enter your mobile number"
                            />
                        </div>
                        <div
                            className="FormField"
                            style={{
                                display: "flex",
                                justifyContent: "center",
                                gap: "40px",
                            }}
                        >
                            <button type="submit" className="FormField__Button">
                                Update
                            </button>
                            <button
                                type="button"
                                onClick={(e) => {
                                    this.handleCancel(e);
                                }}
                                className="FormField__Button"
                            >
                                Cancel
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default EditPatient;

EditAppointment.jsx


import React, { Component } from "react";
import { NavLink } from "react-router-dom";
import NavBar from "./NavBar.jsx";
import { appDetailsData, patientDetailsData } from "./data.js";

import "../App.css";

class EditAppointment extends Component {
    constructor(props) {
        super(props);
        const appointment =
            appDetailsData.getAppointmentDetails(props.match.params.appId) ||
            undefined;
        this.state = {
            name: appointment.name || "",
            disease: appointment.disease || "",
            appdate: appointment.appdate || "",
            slot: appointment.slot || "",
            description: appointment.description || "",
            appointment: appointment,
        };

        this.handleChange = this.handleChange.bind(this);
        this.handleSubmit = this.handleSubmit.bind(this);
        this.handleCancel = this.handleCancel.bind(this);
    }
    handleSubmit(e) {
        console.log(
            "Details",
            this.state.appointment.appId,
            this.state.name,
            this.state.disease,
            this.state.appdate,
            this.state.slot,
            this.state.description
        );
        if (true) {
            e.preventDefault();

            appDetailsData.edit(
                this.state.appointment.appId,
                this.state.name,
                this.state.disease,
                this.state.appdate,
                this.state.slot,
                this.state.description
            );
            this.props.history.push("/allAppointments");
        }
    }

    canBeSubmitted() {
        const { name, disease, appdate, slot, description } = this.state;
        return (
            name.length > 0 &&
            disease.length > 0 &&
            appdate.length > 0 &&
            slot.length > 0 &&
            description.length > 0
        );
    }
    handleCancel(e) {
        this.props.history.push("/allAppointments");
    }
    handleChange(e) {
        let target = e.target;
        let value = target.type === "checkbox" ? target.checked : target.value;
        let name = target.name;

        this.setState({
            [name]: value,
        });
    }

    render() {
        const { appointment } = this.state;
        const names = patientDetailsData.getName();

        if (!appointment) {
            return <h1>No appointments Found</h1>;
        }
        return (
            <div>
                <NavBar />
                <div>
                    <p
                        style={{
                            textAlign: "center",
                            paddingBottom: "10px",
                            paddingTop: "30px",
                            fontSize: "2em",
                        }}
                    >
                        Edit Appointment
                    </p>
                </div>
                <div className="FormCenter">
                    <form onSubmit={this.handleSubmit} className="FormFields">
                        {/*it should have fields like name, disease, appdate, slot, description, submit and cancel buttons */}
                        <div></div>
                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="name">
                                Name of the Patient
                            </label>
                            <input
                                type="text"
                                value={appointment.name}
                                onChange={(e) => this.handleChange(e)}
                                name="name"
                                id="name"
                                className="FormField__Input"
                                placeholder="Enter your name"
                            />
                        </div>

                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="disease"
                            >
                                Disease
                            </label>
                            <input
                                type="text"
                                value={appointment.disease}
                                onChange={(e) => this.handleChange(e)}
                                name="disease"
                                id="disease"
                                className="FormField__Input"
                                placeholder="Enter your disease"
                            />
                        </div>

                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="appdate"
                            >
                                Date
                            </label>
                            <input
                                type="date"
                                value={appointment.appdate}
                                onChange={(e) => this.handleChange(e)}
                                name="appdate"
                                id="appdate"
                                className="FormField__Input"
                                placeholder="Enter your appointment date"
                            />
                        </div>

                        <div className="FormField">
                            <label className="FormField__Label" htmlFor="slot">
                                Slots
                            </label>
                            <input
                                type="text"
                                value={appointment.slot}
                                onChange={(e) => this.handleChange(e)}
                                name="slot"
                                id="slot"
                                className="FormField__Input"
                                placeholder="Enter your slot"
                            />
                        </div>

                        <div className="FormField">
                            <label
                                className="FormField__Label"
                                htmlFor="description"
                            >
                                Disease
                            </label>
                            <input
                                type="text"
                                value={appointment.description}
                                onChange={(e) => this.handleChange(e)}
                                name="description"
                                id="description"
                                className="FormField__Input"
                                placeholder="Enter your description"
                            />
                        </div>

                        <div
                            className="FormField"
                            style={{
                                display: "flex",
                                justifyContent: "center",
                                gap: "40px",
                            }}
                        >
                            <button type="submit" className="FormField__Button">
                                Update
                            </button>
                            <button
                                type="button"
                                onClick={(e) => {
                                    this.handleCancel(e);
                                }}
                                className="FormField__Button"
                            >
                                Cancel
                            </button>
                        </div>
                    </form>
                </div>
            </div>
        );
    }
}

export default EditAppointment;
Share:

Project 2 || Wong Edition || C++ Project || B.Tech Diaries

Project #2 - Sudoku (in conjunction with Algorithm project)

Implement the Sudoku game and the backtracking algorithm using functions from the STL's: <stack>, <pair>.

Solution:


#include <iostream>
#include <fstream>
#include <vector>
#include <stack>

const int N = 9, EMPTY = 0;

class Sudoku
{
private:
    std::vector<std::vector<int>> table;

public:
    Sudoku(std::string filename)
    {
        table.resize(N);
        for (int i = 0; i < N; i++)
        {
            table[i].resize(N);
        }
        get_pattern(filename);
    }

    ~Sudoku() {}

    void get_pattern(std::string filename)
    {
        std::ifstream infile;
        char n;
        int c = 0;
        infile.open(filename);
        while (infile >> n)
        {
            table[c / N][c % N] = n - '0';
            c++;
        }
        infile.close();
    }

    bool is_safe(int row, int col, int num)
    {
        int i, j;

        // Check if the number is already present in the row
        for (i = 0; i < N; i++)
            if (table[row][i] == num || table[i][col] == num)
                return false;

        // Check if the number is already present in the box
        int boxRow = row - row % 3;
        int boxCol = col - col % 3;
        for (i = boxRow; i < boxRow + 3; i++)
            for (j = boxCol; j < boxCol + 3; j++)
                if (table[i][j] == num)
                    return false;

        // If we reach here, it is safe to place the number in the given cell
        return true;
    }

    bool solve()
    {
        std::stack<std::pair<int, int>> st;

        // Find the first empty cell in the grid
        int row = -1, col = -1, r, c, num;
        for (r = 0; r < N; r++)
        {
            for (c = 0; c < N; c++)
            {
                if (table[r][c] == EMPTY)
                {
                    row = r, col = c;
                    break;
                }
            }
            if (row != -1)
                break;
        }

        // If there are no more empty cells, we have solved the puzzle
        if (row == -1)
        {
            return true;
        }

        // Push the first empty cell onto the stack
        st.push({row, col});

        while (!st.empty())
        {
            // Get next empty cell
            std::pair<int, int> p = st.top();
            st.pop();
            row = p.first, col = p.second;

            // Try each number from 1 to 9
            for (num = 1; num <= 9; num++)
            {
                if (is_safe(row, col, num))
                {
                    // Fill cell with number and continue solving
                    table[row][col] = num;
                    if (solve())
                        return true;

                    // Backtrack
                    table[row][col] = EMPTY;
                }
            }
            // No number worked, backtrack
            st.push(p);
            return false;
        }
        return true;
    }
    void print()
    {
        int r, c;
        for (r = 0; r < N; r++)
        {
            for (c = 0; c < N; c++)
            {
                std::cout << table[r][c] << " ";
            }
            std::cout << std::endl;
        }
    }
};

int main(int argc, char const *argv[])
{
    Sudoku s("sudoku_pattern/pattern01.txt");
    std::cout<<"Problem: "<<std::endl;
    s.print();

    std::cout<<"Solution: "<<std::endl;
    if (s.solve())
        s.print();
    else
        std::cout << "No solution" << std::endl;

    return 0;
}

Input (sudoku_pattern/pattern01.txt):


0 0 3 0 2 0 6 0 0
9 0 0 3 0 5 0 0 1
0 0 1 8 0 6 4 0 0
0 0 8 1 0 2 9 0 0
7 0 0 0 0 0 0 0 8
0 0 6 7 0 8 2 0 0
0 0 2 6 0 9 5 0 0
8 0 0 2 0 3 0 0 9
0 0 5 0 1 0 3 0 0

Output:


Problem: 
0 0 3 0 2 0 6 0 0
9 0 0 3 0 5 0 0 1
0 0 1 8 0 6 4 0 0
0 0 8 1 0 2 9 0 0
7 0 0 0 0 0 0 0 8
0 0 6 7 0 8 2 0 0
0 0 2 6 0 9 5 0 0
8 0 0 2 0 3 0 0 9
0 0 5 0 1 0 3 0 0
Solution:
4 8 3 9 2 1 6 5 7
9 6 7 3 4 5 8 2 1
2 5 1 8 7 6 4 9 3
5 4 8 1 3 2 9 7 6
7 2 9 5 6 4 1 3 8
1 3 6 7 9 8 2 4 5
3 7 2 6 8 9 5 1 4
8 1 4 2 5 3 7 6 9
6 9 5 4 1 7 3 8 2

Share:

DBMS Lab - Assignment 1, 2, 3, 4 || KD Edition || B.Tech Diaries

Assignment #1

  1. Create a table called EMP with the following structure:
    Name Type
    EMPNO NUMBER(6)
    ENAME VARCHAR2(20)
    JOB VARCHAR2(10)
    DEPTNO NUMBER(3)
    SAL NUMBER(7, 2)
    Allow NULL for all columns except ENAME and JOB.
    
    CREATE TABLE EMP (
        EMPNO NUMBER(6),
        ENAME VARCHAR2(20) NOT NULL,
        JOB VARCHAR2(10) NOT NULL,
        DEPTNO NUMBER(3),
        SAL NUMBER(7,2)
    );
  2. Add a column experience to the EMP table. EXPERIENCE numeric null allowed.
    
    ALTER TABLE EMP ADD (
        EXPERIENCE NUMBER(2)
    );
  3. Modify the column width of the job field of emp table.
    
    ALTER TABLE EMP MODIFY (
        JOB VARCHAR2(16)
    );
  4. Create the EMP1 table with ENAME and EMPNO, add constraints to check the EMPNO value while entering (i.e.) EMPNO>100
    
    CREATE TABLE EMP1 (
        ENAME VARCHAR2(20) NOT NULL,
        EMPNO NUMBER(6) CONSTRAINT B CHECK(EMPNO>100)
    );

Assignment #2

  1. Write query to select all the columns of emp table.
    
    SELECT * FROM EMP;
    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    7839 KING PRESIDENT - 17-NOV-81 5000 - 10
    7698 BLAKE MANAGER 7839 01-MAY-81 2850 - 30
    7782 CLARK MANAGER 7839 09-JUN-81 2450 - 10
    7566 JONES MANAGER 7839 02-APR-81 2975 - 20
    7788 SCOTT ANALYST 7566 19-APR-87 3000 - 20
    7902 FORD ANALYST 7566 03-DEC-81 3000 - 20
    7369 SMITH CLERK 7902 17-DEC-80 800 - 20
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    7876 ADAMS CLERK 7788 23-MAY-87 1100 - 20
    7900 JAMES CLERK 7698 03-DEC-81 950 - 30
    7934 MILLER CLERK 7782 23-JAN-82 1300 - 10
  2. Write query to select only EMPNAME, ENAME and JOB.
    
    SELECT ENAME, EMPNO, JOB FROM EMP;
    ENAME EMPNO JOB
    KING 7839 PRESIDENT
    BLAKE 7698 MANAGER
    CLARK 7782 MANAGER
    JONES 7566 MANAGER
    SCOTT 7788 ANALYST
    FORD 7902 ANALYST
    SMITH 7369 CLERK
    ALLEN 7499 SALESMAN
    WARD 7521 SALESMAN
    MARTIN 7654 SALESMAN
    TURNER 7844 SALESMAN
    ADAMS 7876 CLERK
    JAMES 7900 CLERK
    MILLER 7934 CLERK
  3. Write query to select unique JOBs.
    
    SELECT UNIQUE JOB FROM EMP;
    JOB
    ANALYST
    CLERK
    SALESMAN
    MANAGER
    PRESIDENT
  4. Write a query to select only those employees who are salesman.
    
    SELECT * FROM EMP 
        WHERE JOB = 'SALESMAN';
    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
    7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
    7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
  5. Select employee name, grade and salary in the order of their salary.
    
    SELECT ENAME, EMPNO, DEPTNO FROM EMP 
        ORDER BY SAL;
    EMPNO ENAME DEPTNO
    SMITH 7369 20
    JAMES 7900 30
    ADAMS 7876 20
    MARTIN 7654 30
    WARD 7521 30
    MILLER 7934 10
    TURNER 7844 30
    ALLEN 7499 30
    CLARK 7782 10
    BLAKE 7698 30
    JONES 7566 20
    FORD 7902 20
    SCOTT 7788 20
    KING 7839 10
  6. Mgmt is considering a pay raise, however they want to find out, if they give a flat $200 increment to all, then what % each person is getting. So in your result, display ENAME, SAL and PCTINCR
    
    SELECT ENAME, SAL, 200/SAL*100 AS PCTINCR 
        FROM EMP;
    ENAME SAL PCTINCR
    KING 5000 4
    BLAKE 2850 7.01754385964912280701754385964912280702
    CLARK 2450 8.16326530612244897959183673469387755102
    JONES 2975 6.72268907563025210084033613445378151261
    SCOTT 3000 6.66666666666666666666666666666666666667
    FORD 3000 6.66666666666666666666666666666666666667
    SMITH 800 25
    ALLEN 1600 12.5
    WARD 1250 16
    MARTIN 1250 16
    TURNER 1500 13.33333333333333333333333333333333333333
    ADAMS 1100 18.18181818181818181818181818181818181818
    JAMES 950 21.05263157894736842105263157894736842105
    MILLER 1300 15.38461538461538461538461538461538461538
  7. Express work experience of each of the employees by using sysdate and hiredate in terms of no of years.
    
    SELECT 
        TO_CHAR(HIREDATE, 'DD.MM.YYYY') "HIREDATE", 
        TO_CHAR(SYSDATE, 'DD.MM.YYYY') "Today", 
        (SYSDATE-HIREDATE)/365 "EXPERIENCE" 
    FROM EMP;
    hiredate Today experience
    17.11.1981 03.01.2023 41.15791945712836123795027904616945712849
    01.05.1981 03.01.2023 41.70586466260781329274479959411466260795
    09.06.1981 03.01.2023 41.59901534753932014205986808726534753945
    02.04.1981 03.01.2023 41.78531671740233384069000507356671740247
    19.04.1987 03.01.2023 35.73600164890918315575849822425164890932
    03.12.1981 03.01.2023 41.11408384069000507356671740233384069014
    17.12.1980 03.01.2023 42.07572767630644342973110096397767630658
    20.02.1981 03.01.2023 41.89764548452562151192288178589548452575
    22.02.1981 03.01.2023 41.89216603247082699137493658041603247096
    28.09.1981 03.01.2023 41.29490575849822425164890918315575849836
    08.09.1981 03.01.2023 41.3497002790461694571283612379502790463
    23.05.1987 03.01.2023 35.64285096397767630644342973110096397781
    03.12.1981 03.01.2023 41.11408384069000507356671740233384069014
    23.01.1982 03.01.2023 40.97435781329274479959411466260781329288

Assignment #3

  1. List down number of employees, minimum salary, maximum salary for each department.
    
    SELECT 
        COUNT(ENAME) AS COUNT, MAX(SAL) AS MAX, MIN(SAL) AS MIN, DEPTNO 
        FROM EMP 
        GROUP BY DEPTNO;
    COUNT MAX MIN DEPTNO
    6 2850 950 30
    3 5000 1300 10
    5 3000 800 20
  2. Update Email ID, if department id is
    1. <= 10, update the EMAIL field by appending @oracle.com
      
      ALTER TABLE EMP ADD(
          EMAIL VARCHAR(32)
      );
      UPDATE EMP 
          SET EMAIL=CONCAT(LOWER(ENAME),'@oracle.com') 
          WHERE DEPTNO<=10;
    2. <= 20, update the EMAIL field by appending @oracle.co.uk
      
      UPDATE EMP 
          SET EMAIL=CONCAT(LOWER(ENAME),'@oracle.co.uk') 
          WHERE DEPTNO>10 AND DEPTNO<=20;
    3. Else update it as @oracle.co.in
      
      UPDATE EMP 
          SET EMAIL=CONCAT(LOWER(ENAME),'@oracle.co.in') 
          WHERE DEPTNO>20;
      SELECT ENAME, EMAIL FROM EMP;
      ENAME EMAIL
      KING king@oracle.com
      BLAKE blake@oracle.co.in
      CLARK clark@oracle.com
      JONES jones@oracle.co.uk
      SCOTT scott@oracle.co.uk
      FORD ford@oracle.co.uk
      SMITH smith@oracle.co.uk
      ALLEN allen@oracle.co.in
      WARD ward@oracle.co.in
      MARTIN martin@oracle.co.in
      TURNER turner@oracle.co.in
      ADAMS adams@oracle.co.uk
      JAMES james@oracle.co.in
      MILLER miller@oracle.com
    4. Apart from 'Delete' and 'Truncate' statement can also be used for deleting the rows. Comment on their difference.
      When execute the DELETE command, the DBMS logs all removed rows. This means it is easier to recover from a mistake, than it would a mistaken TRUNCATE. When we TRUNCATE a table, less information is logged. This means the TRUNCATE statement executes very fast; however, it does so at the expense of not logging each row deleted.
    5. Display a department id wise count of employees
      • Getting salary more than 1200
        
        SELECT DEPTNO, COUNT(1) AS "COUNT" FROM EMP 
        WHERE SAL > 1200 
        GROUP BY DEPTNO;
        
        DEPTNO COUNT
        30 5
        10 3
        20 3
      • Apart from the above condition, select only those departments which has an average salary in excess of 1600
        
            SELECT DEPTNO, COUNT(1) AS "COUNT" FROM EMP
            WHERE SAL > 1200 
            HAVING AVG(SAL) > 1600
            GROUP BY DEPTNO;
        DEPTNO COUNT
        30 5
        10 3
        20 3
    6. Explain how two levels of filtering is happening based on firstly WHERE clause secondly HAVING clause based on this particular scenario.
      HAVING clause is used for cases where there are aggregate functions like COUNT, SUM, AVG, etc. WHERE clause is used for extract only those records that fulfil a specified condition and works for binary operation only.
    7. We want to add a new row in the employees table with employee id 1000, First Name = 'Scott', Last Name = 'Tiger', Email = 'Stiger@oracle.co.uk', HireDate = 01/02/2014, Job id = 'PR_Prsdnt'(Title = 'Company President')
      
      INSERT INTO EMP(EMPNO, DEPTNO, ENAME, EMAIL, HIREDATE, JOB, SAL) 
      VALUES(1000, 80, 'SCOTT', 'Stiger@oracle.co.uk', TO_DATE('01/02/2014','dd/mm/yyyy'), 'PR_PRSDNT', 5000);
    8. Issue necessary insert statements
      
      INSERT INTO DEPT(DEPTNO, DNAME, LOC)
      VALUES(80, 'DATABASE', 'EDINBURGH');
    9. After the update is over in the email column, use INSTR and SUBSTR to display email id and domain information separately.
      
      SELECT 
          SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1) AS EMAIL_ID, 
          SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) AS DOMAIN 
      FROM EMP WHERE EMPNO=1000;
      EMAIL_ID DOMAIN
      Stiger oracle.co.uk
    10. Display day, month and year of the hire date of the employees.
      
      SELECT EXTRACT(DAY FROM TO_DATE(HIREDATE, 'dd-mm-rr')) AS DAY, 
      EXTRACT(MONTH FROM TO_DATE(HIREDATE, 'dd-mm-rr')) AS MONTH, 
      EXTRACT(YEAR FROM TO_DATE(HIREDATE, 'dd-mm-rr')) AS YEAR FROM EMP;
      DAY MONTH YEAR
      17 11 1981
      1 5 1981
      9 6 1981
      2 4 1981
      19 4 1987
      3 12 1981
      17 12 1980
      20 2 1981
      22 2 1981
      28 9 1981
      8 9 1981
      23 5 1987
      3 12 1981
      23 1 1982
      1 2 2014

Assignment #4

Level I

  1. Display name of employees, department name and job name for each employee.
    
    SELECT ENAME, DNAME, JOB FROM EMP E, DEPT D 
        WHERE E.DEPTNO=D.DEPTNO;
    ENAME DNAME JOB
    KING ACCOUNTING PRESIDENT
    BLAKE SALES MANAGER
    CLARK ACCOUNTING MANAGER
    JONES RESEARCH MANAGER
    SCOTT RESEARCH ANALYST
    FORD RESEARCH ANALYST
    SMITH RESEARCH CLERK
    ALLEN SALES SALESMAN
    WARD SALES SALESMAN
    MARTIN SALES SALESMAN
    TURNER SALES SALESMAN
    ADAMS RESEARCH CLERK
    JAMES SALES CLERK
    MILLER ACCOUNTING CLERK
    SCOTT DATABASE PR_PRSDNT
  2. Display the department name along with no of employees and average salary of that department.
    
    SELECT DNAME, CNT, AVG_SAL
    FROM (
        SELECT DEPTNO, COUNT(*) AS CNT, AVG(SAL) AS AVG_SAL FROM EMP GROUP BY DEPTNO 
    ) E 
    INNER JOIN DEPT D
    ON D.DEPTNO = E.DEPTNO;
    DNAME CNT AVG_SAL
    SALES 6 1566.666666666666666666666666666666666667
    ACCOUNTING 3 2916.666666666666666666666666666666666667
    RESEARCH 5 2175
    DATABASE 1 5000
  3. For each department, find out no. of jobs the employees are assigned to
    
    SELECT JOBS, DNAME 
    FROM (
      SELECT COUNT(UNIQUE JOB) AS JOBS, DEPTNO FROM EMP GROUP BY DEPTNO
    ) E, DEPT D 
    WHERE E.DEPTNO=D.DEPTNO;
    JOBS DNAME
    3 SALES
    3 ACCOUNTING
    3 RESEARCH
    1 DATABASE
  4. Check for correctness of the above queries in terms of count, if you want to bring in all entries, how would you achieve the same?
    
    SELECT * FROM EMP;
  5. Group by the employees based on the first character of employee first name. Display the results in alphabetic order (descending) of first character.
    
    SELECT SUBSTR(ENAME, 1, 1) AS ALPHA, COUNT(1) AS CNT FROM EMP 
    GROUP BY SUBSTR(ENAME, 1, 1) 
    ORDER BY SUBSTR(ENAME, 1, 1) DESC;
    ALPHA CNT
    W 1
    T 1
    S 3
    M 2
    K 1
    J 2
    F 1
    C 1
    B 1
    A 2

Level II

Table - EmployeeDetails

EmpId FullName ManagerId DateOfJoining
121 John Snow 321 01/31/2014
321 Walter White 986 01/30/2015
421 Kuldeep Rana 876 27/11/2016

Table - EmployeeSalary

EmpId Project Salary
121 P1 8000
321 P2 1000
421 P1 12000

CREATE TABLE EMPSAL (EMPID NUMBER(3), 
    PROJECT VARCHAR(2), 
    SAL NUMBER(7,2), 
    CONSTRAINT PK_EMPID PRIMARY KEY(EMPID) 
);
CREATE TABLE EMPDET  
(
    EMPID NUMBER(3),  
    FNAME VARCHAR(16),  
    MGID NUMBER(3), 
    JDT DATE, 
    CONSTRAINT PK_MGID PRIMARY KEY(MGID), 
    CONSTRAINT FK_EMPID FOREIGN KEY(EMPID) REFERENCES EMPSAL(EMPID) 
);
INSERT INTO EMPSAL VALUES(121, 'P1', 8000);
INSERT INTO EMPSAL VALUES(321, 'P2', 1000);
INSERT INTO EMPSAL VALUES(421, 'P1', 12000);
INSERT INTO EMPDET VALUES(121, 'JOHN SNOW', 321, DATE '2014-01-31');
INSERT INTO EMPDET VALUES(321, 'WALTER WHITE', 986, DATE '2015-01-30');
INSERT INTO EMPDET VALUES(421, 'KULDEEP RANA', 876, DATE '2016-11-27');
  1. Write a SQL query to fetch the count of employees working in project 'P1'.
    
    SELECT COUNT(*) AS COUNT FROM EMPSAL 
        WHERE PROJECT='P1';
    COUNT
    2
  2. Write a SQL query to fetch employee names having salary greater than or equal to 5000 and less than or equal 10000.
    
    SELECT FNAME FROM EMPSAL 
        INNER JOIN EMPDET ON EMPSAL.EMPID=EMPDET.EMPID 
        WHERE EMPSAL.SAL BETWEEN 5000 AND 10000;
    FNAME
    John Snow
  3. Write a SQL query to fetch project-wise count of employees sorted by project's count in descending order.
    
    SELECT PROJECT, COUNT(*) AS COUNT FROM EMPSAL 
        GROUP BY PROJECT 
        ORDER BY COUNT DESC;
    PROJECT COUNT
    P1 2
    P2 1
  4. Write a query to fetch only the first name(string before space) from the Full Name column of EmployeeDetails table.
    
    SELECT SUBSTR(FNAME,1,INSTR(FNAME,' ')-1) AS FIRSTNAME 
        FROM EMPDET;
    FIRSTNAME
    John
    Walter
    Kuldeep
  5. Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.
    
    SELECT FNAME, SAL FROM EMPDET 
    LEFT JOIN EMPSAL ON EMPSAL.EMPID=EMPDET.EMPID;
    FNAME SAL
    John Snow 8000
    Walter White 1000
    Kuldeep Rana 12000
  6. Write a SQL query to fetch all the Employees who are also managers from EmployeeDetails table.
    
    SELECT * FROM EMPDET E 
    WHERE EXISTS (SELECT * FROM EMPDET S WHERE E.EMPID = S.MGID);
    EMPID FNAME MGID JDT
    321 Walter White 986 30-JAN-15
  7. Write a SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.
    
    SELECT * FROM EMPDET E 
    WHERE EXISTS (SELECT * FROM EMPSAL S WHERE E.EMPID = S.EMPID);
    EMPID FNAME MGID JDT
    121 John Snow 321 31-JAN-14
    321 Walter White 986 30-JAN-15
    421 Kuldeep Rana 876 27-NOV-16
  8. Write a SQL query to fetch duplicate records from a table.
    
    SELECT EMPID, FNAME, MGID, JDT FROM EMPDET 
        GROUP BY EMPID, FNAME, MGID, JDT 
        HAVING COUNT(*) > 1;
  9. Write a SQL query to remove duplicates from a table without using temporary table.
    
    DELETE FROM EMPSAL 
    WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMPSAL GROUP BY EMPID);
  10. Write a SQL query to fetch only odd rows from table.
    
    SELECT E.EMPID, E.PROJECT, E.SAL FROM ( 
    SELECT ROW_NUMBER() OVER(ORDER BY EMPID) AS ROWNUMBER, EMPID, PROJECT, SAL FROM EMPSAL) E 
    WHERE MOD(E.ROWNUMBER, 2) = 1;
    EMPID PROJECT SAL
    121 P1 8000
    421 P1 12000
  11. Write a SQL query to fetch only even rows from table.
    
    SELECT E.EMPID, E.PROJECT, E.SAL FROM ( 
    SELECT ROW_NUMBER() OVER(ORDER BY EMPID) AS ROWNUMBER, EMPID, PROJECT, SAL FROM EMPSAL) E 
    WHERE MOD(E.ROWNUMBER, 2) = 0;
    EMPID PROJECT SAL
    321 P2 1000
  12. Write a SQL query to create a new table with data and structure copied from another table.
    
    SELECT * INTO EMPSALARY 
        FROM EMPSAL;
  13. Write a SQL query to create an empty table with same structure as some other table.
    
    SELECT * INTO EMPSALARY FROM EMPSAL 
        WHERE 1=0;
  14. Write a SQL query to fetch common records between two variables.
    
    SELECT * FROM EMPSAL 
        INTERSECT SELECT * FROM MGSAL;
  15. Write a SQL query to fetch records that are present in one table but not in another table.
    
    SELECT * FROM EMPSAL 
        MINUS SELECT * FROM MGSAL;
  16. Write a SQL query to find current date-time.
    
    SELECT getdate();
  17. Write a SQL query to fetch all the Employees details from EmployeeDetails table who joined in year 2016.
    
    SELECT * FROM EMPDET 
    WHERE JDT BETWEEN DATE '2016-01-01' AND DATE '2016-12-31';
    EMPID FNAME MGID JDT
    421 Kuldeep Rana 876 27-NOV-16
  18. Write a SQL query to fetch top n records.
    
    SELECT * FROM EMPSAL 
        ORDER BY SAL DESC 
        FETCH NEXT 2 ROWS ONLY;
    EMPID PROJECT SAL
    421 P1 12000
    121 P1 8000
  19. Write a SQL query to find the nth highest salary from table.
    
    SELECT * FROM (
        SELECT * FROM EMPSAL ORDER BY SAL DESC FETCH NEXT 2 ROWS ONLY
    ) ORDER BY SAL ASC 
    FETCH NEXT 1 ROWS ONLY;
    EMPID PROJECT SAL
    121 P1 8000
  20. Write SQL query to find the 3rd highest salary from table without using TOP/limit keyword.
    
    SELECT SAL 
        FROM EMPSAL EMP1 
        WHERE 2 = ( 
            SELECT COUNT( DISTINCT( EMP2.SAL ) ) FROM EMPSAL EMP2 
            WHERE EMP2.SAL > EMP1.SAL 
        );
    SAL
    1000
Share: